Update的另一种写法
最近学到update的另一种写法，是以前自己从来没有使用过的，看了一下文档，也没有找到很详细的说明。这种update方式其基础是建立在Query中的，所以可以确保使用CBO而非RBO，可以在大表的更新时明显得提高效率。在这里记录一下基本的方法：
SQL> create table a ( id int, a1 varchar2(25) );
SQL> create table b ( id int, b1 varchar2(25) );
SQL> insert into a values ( 1, 'Hello' );
SQL> insert into a values ( 2, 'xxxxx' );
SQL> insert into b values ( 2, 'World' );
SQL> commit;
SQL> update ( select a1, b1 from a, b where a.id = b.id )
2 set a1 = b1;
set a1 = b1
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table
--无法Update,必须要有一个主键
SQL> alter table b add constraint b_key primary key(id);
SQL> update ( select a1, b1 from a, b where a.id = b.id )
2 set a1 = b1;
1 row updated.
--可以Update
SQL> update ( select a1, b1 from a, b where a.id = b.id )
2 set b1 = a1;
set b1 = a1
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table
--交换位置后依旧无法更新
SQL> alter table b drop constraint b_key;
SQL> alter table a add constraint a_key primary key(id);
SQL> update ( select a1, b1 from a, b where a.id = b.id )
2 set b1 = a1;
1 row updated.
--为表a设置主键后可以更新
SQL> alter table a drop constraint a_key;
SQL> alter table a add constraint a_key primary key(id,a1);
SQL> update ( select a1, b1 from a, b where a.id = b.id )
2 set b1 = a1;
set b1 = a1
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table
--使用联合主键也是不可以的，必须是关联字段
由上可知，使用这种方式来更新表，需要用于更新的表(最终数据表)的关联字段必须设置为主键，且不可多字段主键。另外还有一个网友也指出了另外一个问题：
If the user has update permission on table A, but only has select permission on table B, they cannot update via the first example. Oracle will return ORA-01031 (insufficient privileges).
测试一下：
SQL> create user x identified by x;
SQL> grant create session on x;
SQL> grant select,update on a to x;
SQL> grant select on b to x;
SQL> create public synonym a for wangxiaoqi.a;
SQL> create public synonym b for wangxiaoqi.b;
SQL> conn x/x
Connected.
SQL> update ( select a1, b1 from a, b where a.id = b.id )
2 set a1 = b1;
update ( select a1, b1 from a, b where a.id = b.id )
*
ERROR at line 1:
ORA-01031: insufficient privileges
--系统报错权限不够
SQL> update a set a1 = (select b1 from b where b.id=a.id);
2 rows updated.
--使用Update...Select...语句是可以更新成功的