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...語句是可以更新成功的
?
?
?
?