最近学到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 )
2set 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 )
2set a1 = b1;

1 row updated.

--可以Update

SQL> update ( select a1, b1 from a, b where a.id = b.id )
2set 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 )
2set 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 userx 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> connx/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...语句是可以更新成功的