仁兄,update语句不能这样写啊,不可以更新你select出来的虚表
Cause: An attempt was made to insert or update columns of a join view which map to a non-key-preserved table. Action: Modify the underlying base tables directly.
Cause: An attempt was made to insert or update columns of a join view which map to a non-key-preserved table. Action: Modify the underlying base tables directly.
have a 1.8 million record table built from all_objects (over and over)..
object_id was set to rownum -- so it started unique. I did this tho:big_table@ORA920> insert into big_table ( owner, object_name, object_id,
created, last_ddl_time, object_type )
2 select owner, object_name, object_id, created, last_ddl_time, 'XXX'
3 from big_table where owner = 'BIG_TABLE';
big_table@ORA920> alter table big_table add constraint unq unique( object_id,
object_type );big_table@ORA920> create table convert
2 as
3 select distinct object_type old_object_type, decode( object_type, 'XXX',
'TABLE', object_type ) new_object_type
4 from big_table;big_table@ORA920> alter table convert add constraint convert_pk primary
key(old_object_type);
(that'll create some DUP object_id, object_types in my big table when I
'convert' them).... Here is how your mass update should run then:big_table@ORA920> alter table big_table
2 disable constraint unq;Table altered.Elapsed: 00:00:01.44
big_table@ORA920>
big_table@ORA920> update ( select big_table.object_type old_object_type,
2 convert.new_object_type new_object_type
3 from big_table, convert
4 where big_table.object_type = convert.old_object_type )
5 set old_object_type = new_object_type
6 /1833984 rows updated.这个怎么讲?虚表还是可以更新的阿 :)
set a.a2=(select b.b2 from b where a.a1=b.b1)
where exists (select b.b2 from b where a.a1=b.b1)