Oracle 两表关联更新

 有TA, TB两表，假设均有三个栏位id, name, remark. 现在需要把TB表的name, remark两个栏位通过id关联，更新到TA表的对应栏位。

建表脚本：

[sql] view plain copy

1. drop table TA;  
2. create table TA  
3. (  
4. id number not null,  
5. name varchar(10) not null,  
6. remark varchar(10) not null  
7. );  
8.   
9. drop table TB;  
10. create table TB  
11. (  
12. id number not null,  
13. name varchar(10) not null,  
14. remark varchar(10) not null  
15. );  
16.   
17. truncate table TA;  
18. insert into TA values(1, 'Aname1', 'Aremak1');  
19. insert into TA values(2, 'Aname2', 'Aremak2');  
20. commit;  
21.   
22. truncate table TB;  
23. insert into TB values(1, 'Bname1', 'Bremak1');  
24. insert into TB values(3, 'Bname3', 'Bremak3');  
25. commit;  
26.   
27. select * from TA;  
28. select * from TB;  

SQLServer/Oracle版本的Update写法分别如下：

1. SQLServer

[sql] view plain copy

1. update TA set name=b.name, remark=b.remark from TA a inner join TB b on a.id = b.id  

或者

[sql] view plain copy

1. update TA set name=b.name, remark=b.remark from TA a, TB b where a.id = b.id  

注意不要在被更新表的的栏位前面加别名前缀，否则语法静态检查没问题，实际执行会报错。

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.name" could not be bound.

2. Oracle

[sql] view plain copy

1. update TA a set(name, remark)=(select b.name, b.remark from TB b where b.id=a.id)   
2. where exists(select 1 from TB b where b.id=a.id)  

注意如果不添加后面的exists语句，TA关联不到的行name, remark栏位将被更新为NULL值， 如果name, remark栏位不允许为null，则报错。 这不是我们希望看到的。

[sql] view plain copy

1. --when name, remark is not null, cause error.   
2. --if allow null, rows in TA not matched will be update to null.  
3. update TA a set(name, remark)=(select b.name, b.remark from TB b where b.id=a.id);  

可考虑的替代方法：

[sql] view plain copy

1. update TA a set name= nvl((select b.name from TB b where b.id=a.id), a.name);  
2. update TA a set remark= nvl((select b.remark from TB b where b.id=a.id), a.remark);  

如果TA.id, TB.id是unique index或primary key

可以使用视图更新的语法：

[sql] view plain copy

1. ALTER TABLE TA ADD CONSTRAINT TA_PK  
2.   PRIMARY KEY (  
3.   ID  
4. )  
5.  ENABLE  
6.  VALIDATE  
7. ;  
8.   
9. ALTER TABLE TB ADD CONSTRAINT TB_PK  
10.   PRIMARY KEY (  
11.   ID  
12. )  
13.  ENABLE  
14.  VALIDATE  
15. ;  
16.   
17. update (select a.name, b.name as newname,   
18. a.remark, b.remark as newremark from TA a, TB b where a.id=b.id)  
19. set name=newname, remark=newremark;                                                           
20. 
    
21. 
    
22. -------------------------------------------------------------
23. Oracle有下面两个表:将表tab1中id值与和表tab2中id值相同的行的val更新为tab2中val的值.
    select * from tab1;

    
    

    select * from tab2
    

    

    最容易犯的错误是:update tab1 set val=(select val from tab2 where tab1.id=tab2.id);
    更新完后的结果是:select * from tab1,在tab1中有的行,如果在tab2中没有对应的行,值被更新为null

    

    改正为:update tab1 set val=(select val from tab2 where tab1.id=tab2.id)
    where exists (select 1 from tab2 where tab1.id=tab2.id)
    但是如果tab2中有多条对应tab1中一条的情况也会出错.
    最好的方法是用merge语法:
    1. merge into tab1  
    2. using tab2  
    3. on(tab1.id=tab2.id)  
    4. when matched then  
    5. update set tab1.val = tab2.val  
    同样,如果tab2中有多条对应tab1中一条的情况也会出错:ORA-30926: unable to get a stable set of rows in the source tables
    比如在tab2中再插入一条 insert into tab2 values(2,'xxxx')
    可以通过在using中的subquery中将重复记录过滤来避免这种错误,merge终极版:
    1. merge into tab1  
    2. using  (select * FROM tab2 X  WHERE  X.ROWID =  
    3. (SELECT MAX(Y.ROWID) FROM  tab2 Y  WHERE  X.ID = Y.ID)) tab2  
    4. on(tab1.id=tab2.id)  
    5. when matched then  
    6. update set tab1.val = tab2.val  
    
    
24.