当前位置:数据库 > Oracle >>

[Oracle] 多表关联的update和delete

[Oracle] 多表关联的update和delete
 
由于Oracle不支持update或delete from语句,因此,Oracle的多表关联update和delete必须借助于子查询,同理,Oracle也不支持同时update或delete多张表,其典型用法如下:
多表关联update
首先,构造测试表和数据如下:
[sql] 
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
[sql] 
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,关联条件同上
[sql] 
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条数据,将会出现如下错误:
[plain] 
ORA-01427: single-row subquery returns more than one row  
这时候,你就必须得对子查询里的返回条数进行限定,如rownum=1或distinct等方法。
这里的where exists字句是不可以省略的,否则将update全表!这里要千万小心。
如果查看执行计划,你会发现上述的update语句将扫描testb表两次,如果该表比较大的话,对性能会有影响,如果只想扫描一次的话,可以用如下方法代替:
[sql] 
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);  

 

 
多表关联delete
1)利用in或not in删除数据
[sql] 
delete from testa where table_name   
in (select object_name from testb);  
2)利用exists或not exists删除数据
[sql] 
delete from testb b  
where exists   
(select 1 from testa a where a.owner=b.owner and a.table_name=b.object_name)  

 

 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,