表連線時update與delete操作需注意的地方

剛剛在論壇上又看到一則很老的精華貼子,經測試後越發覺的好,於是總結了下,發上來了,以後備用,呵~~~[@more@]

一. 表連線的update測試
/*******************先看下面的測試******************/
SQL> drop table test1;

表已刪除。

SQL> drop table test;

表已刪除。

SQL> create table test(a number,b number,c number ,d varchar2(6));

表已建立。

SQL> insert into test values(1,1,1,'1');

已建立 1 行。

SQL> insert into test values(2,2,2,'2');

已建立 1 行。

SQL> insert into test values(3,3,3,'3');

已建立 1 行。

SQL> commit;

提交完成。

SQL> create table test1(a number,b number);

表已建立。

SQL> insert into test1 values(1,9);

已建立 1 行。

SQL> commit;

提交完成。

SQL> update (select a.a aa,a.b ab,b.a ba,b.b bb from test a,test b where a.a=b.a) set ab=bb;
update (select a.a aa,a.b ab,b.a ba,b.b bb from test a,test b where a.a=b.a) set ab=bb
*
第 1 行出現錯誤:
ORA-01779: 無法修改與非鍵值儲存表對應的列
/********給test表新增主鍵a列***************/
SQL> alter table test add primary key(a);

表已更改。

SQL> update(select a.a aa,a.b ab,b.a ba,b.b bb from test a,test1 b where a.a=b.a) set ab=bb;
update(select a.a aa,a.b ab,b.a ba,b.b bb from test a,test1 b where a.a=b.a) set ab=bb
*
第 1 行出現錯誤:
ORA-01779: 無法修改與非鍵值儲存表對應的列
提示:還是無法更新,因為更新的是test表,test表是根據test1表的a列來更新的,所以應該是給test1表的a列設定主鍵,下面換作更新test1表,只需將set ab=bb 改為 set bb=ab即可,如下:

SQL> update(select a.a aa,a.b ab,b.a ba,b.b bb from test a,test1 b where a.a=b.a) set
2 bb=ab;

已更新 1 行。

SQL> select * from test;

A B C D
---------- ---------- ---------- ------
1 1 1 1
2 2 2 2
3 3 3 3

SQL> select * from test1;

A B
---------- ----------
1 1

SQL> rollback;

回退已完成。

SQL> select * from test1;

A B
---------- ----------
1 9
/*********新增test1表主鍵a列******************/
SQL> alter table test1 add primary key(a);

表已更改。

SQL> update(select a.a aa,a.b ab,b.a ba,b.b bb from test a,test1 b where a.a=b.a) set
2 ab=bb;

已更新 1 行。

SQL> select * from test;

A B C D
---------- ---------- ---------- ------
1 9 1 1
2 2 2 2
3 3 3 3

SQL> select * from test1;

A B
---------- ----------
1 9

SQL> rollback;

回退已完成。

SQL> spool off

二. 表連線的delete測試
SQL> select *from test;

A B C D
---------- ---------- ---------- ------
1 1 1 1
2 2 2 2
3 3 3 wew

SQL> select *from test1;

A B
---------- ----------
1 1

SQL> delete(select a.a aa,a.b ab,b.a ba,b.b bb from test a,test1 b where a.a=b.a);

已刪除 1 行。

SQL> select * from test;

A B C D
---------- ---------- ---------- ------
2 2 2 2
3 3 3 wew

SQL> select *from test1;

A B
---------- ----------
1 1

SQL> rollback;

回退已完成。

SQL> select *form test;
select *form test
*
第 1 行出現錯誤:
ORA-00923: 未找到要求的 FROM 關鍵字

SQL> select * from test;

A B C D
---------- ---------- ---------- ------
1 1 1 1
2 2 2 2
3 3 3 wew

SQL> delete(select a.a aa,a.b ab,b.a ba,b.b bb from test a,test1 b where b.a=a.a);

已刪除 1 行。

SQL> select * from test;

A B C D
---------- ---------- ---------- ------
2 2 2 2
3 3 3 wew

SQL> select * from test1;

A B
---------- ----------
1 1

SQL> rollback;

回退已完成。

SQL> alter table test drop primary key;

表已更改。

SQL> delete(select a.a aa,a.b ab,b.a ba,b.b bb from test a,test1 b where b.a=a.a);

已刪除 1 行。

SQL> select *form test;
select *form test
*
第 1 行出現錯誤:
ORA-00923: 未找到要求的 FROM 關鍵字

SQL> select * from test;

A B C D
---------- ---------- ---------- ------
2 2 2 2
3 3 3 wew

SQL> select * from test1;

A B
---------- ----------
1 1

SQL> rollback;

回退已完成。

SQL> delete(select a.a aa,a.b ab,b.a ba,b.b bb from test1 b,test a where b.a=a.a);

已刪除 1 行。

SQL> select * from test;

A B C D
---------- ---------- ---------- ------
2 2 2 2
3 3 3 wew

SQL> select *from test1;

A B
---------- ----------
1 1

SQL> rollback;

回退已完成。

SQL> alter table test1 drop primary key;

表已更改。

SQL> delete(select a.a aa,a.b ab,b.a ba,b.b bb from test a,test1 b where b.a=a.a);
delete(select a.a aa,a.b ab,b.a ba,b.b bb from test a,test1 b where b.a=a.a)
*
第 1 行出現錯誤:
ORA-01752: 不能從沒有一個鍵值儲存表的檢視中刪除

SQL> alter table test add primary key(a);

表已更改。

SQL> delete(select a.a aa,a.b ab,b.a ba,b.b bb from test a,test1 b where b.a=a.a);

已刪除 1 行。

SQL> select * from test;

A B C D
---------- ---------- ---------- ------
1 1 1 1
2 2 2 2
3 3 3 wew

SQL> select *from test1;

未選定行

SQL> rollback;

回退已完成。

SQL> alter table test1 add primary key(a);

表已更改。

SQL> delete(select a.a aa,a.b ab,b.a ba,b.b bb from test a,test1 b where b.a=a.a);

已刪除 1 行。

SQL> select *from test;

A B C D
---------- ---------- ---------- ------
2 2 2 2
3 3 3 wew

SQL> select *from test1;

A B
---------- ----------
1 1

SQL> rollback;

回退已完成。

SQL> delete(select a.a aa,a.b ab,b.a ba,b.b bb from test1 b,test a where b.a=a.a);

已刪除 1 行。

SQL> select * from test;

A B C D
---------- ---------- ---------- ------
1 1 1 1
2 2 2 2
3 3 3 wew

SQL> select * from test1;

未選定行

SQL> rollback;

回退已完成。

SQL> spool off;
總結:
1.當其中只有一個表有主鍵的時候
刪除沒有主鍵的表記錄
2.當兩個都有主鍵的時候
刪除表連線中非驅動表的那個表
比如通常的rule模式下from a,b : 就刪除a中的,如果是from b,a就刪除b中的

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21251711/viewspace-1020424/，如需轉載，請註明出處，否則將追究法律責任。