Oracle多表關聯的update和delete

由於Oracle不支援update或delete from語句，因此，Oracle的多表關聯update和delete必須藉助於 子查詢，同理，Oracle也不支援同時update或delete多張表，其典型用法如下：

多表關聯update

首先，構造測試表和資料如下：

SYS@TEST16> create table testa as select owner,table_name,status from dba_tables;        Table created.  SYS@TEST16> create table testb as select owner,object_name,status from dba_objects;        Table created.

1）更新testa表的status=‘VALID’，關聯條件為testa.owner=testb.owner and testa.table_name=testb.table_name

update testa a set status='VALID'

where exists (select 1 from testb b where a.owner=b.owner and a.table_name=b.object_name);

2）更新testa表的status等於testb表的status，關聯條件同上

update testa a

set a.status=(select b.status from testb b where a.owner=b.owner and a.table_name=b.object_name)

where exists (select 1 from testb b where a.owner=b.owner and a.table_name=b.object_name);

這裡要注意的是：只有當子查詢每次只返回1條或0條資料時，上述語句才能執行成功，如果返回超 過1條資料，將會出現如下錯誤：

ORA-01427: single-row subquery returns more than one row

這時候，你就必須得對子查詢裡的返回條數進行限定，如rownum=1或distinct等方法。

這裡的 where exists字句是不可以省略的，否則將update全表！這裡要千萬小心。

如果查看執行計畫，你 會發現上述的update語句將掃描testb表兩次，如果該表比較大的話，對效能會有影響，如果只想掃描 一次的話，可以用如下方法代替：

update testa a

set a.status=nvl((select b.status from testb b where a.owner=b.owner and a.table_name=b.object_name),a.status);

update自身列

有時候，可能需要利用自身的值更新自身的列，比如表test的列col1，col2有空格 ，我們需要trim去除空格，這時候我們就可以利用rowid更新，update語句如下：

update test a  set (col1,col2)=  (select trim(b.col1),trim(b.col2) from test b  where  a.rowid=b.rowid)  where exists  (select 1 from test b  where  a.rowid=b.rowid)

多表關聯delete

1）利用in或not in刪除資料

delete from testa where table_name

in (select object_name from testb);

2）利用exists或not exists刪除資料

delete from testb b  where exists   (select 1 from testa a where a.owner=b.owner and a.table_name=b.object_name)

查看本欄目更多精彩內容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/