【SQL update 多表关联更新方法总结】

1. MySQL

update test1,test2 
set test1.name=test2.name,test1.age=test2.age
where test1.id=test2.id
1
2
3

2. oracle

1. 多表关联，更新全表中的某一字段
EX1：

UPDATE REDREPORT T  SET T.SSHY=(SELECT  D.SSHY FROM DATA_COMPANY D WHERE T.DWDM =D.DWDM 
and rownum=1 );
1
2

EX2：

update test1 
set (test1.name,test1.age)=
(select test2.name,test2.age from test2 where test2.id=test1.id)
1
2
3

2. 多表关联，更新指定行的某一字段

UPDATE "DATA_HISTORY_copy2" t 
set SHJG = (select m.SHJG from DATA_MONTHCOPY m where t.DWDM = m.DWDM and SUBSTR(t.SQSJ, 0, 8) = SUBSTR(m.SQSJ, 0, 8)) 
WHERE EXISTS (select t.* from "DATA_HISTORY_copy2" t,DATA_MONTHCOPY m where t.DWDM = m.DWDM and SUBSTR(t.SQSJ, 0, 8) = SUBSTR(m.SQSJ, 0, 8))
1
2
3

3. SQLServer

update test1
set test1.name=test2.name,test1.age=test2.age
from test1 
inner join test2
on test1.id=test2.id
1
2
3
4
5

4. 通用方法

update test1 
set name=(select name from test2 where test2.id=test1.id),
age=(select age from test2 where test2.id=test1.id)
1
2
3

引用

【SQL】sql update 多表关联更新方法总结.