update test1
set(test1.name,test1.age)=(select test2.name,test2.age from test2 where test2.id=test1.id)whereexists(select1from test2 where test2.id=test1.id)
MS SQL Server不支持这样的语法,相对应的写法为:
update test1
set test1.name=test2.name,test1.age=test2.age
from test1
innerjoin test2
on test1.id=test2.id
update test1
set test1.name=test2.name,test1.age=test2.age
from test1,test2
where test1.id=test2.id
MySQL多表更新方法:
update test1 innerjoin test2
set test1.name=test2.name,test1.age=test2.age
where test1.id=test2.id
update test1,test2
set test1.name=test2.name,test1.age=test2.age
where test1.id=test2.id
通用方法(效率较低):
update test1
set name=(select name from test2 where test2.id=test1.id),
age=(select age from test2 where test2.id=test1.id)
SQL Update的四种常见写法:Oralce和DB2都支持的语法:update test1 set (test1.name,test1.age)=(select test2.name,test2.age from test2 where test2.id=test1.id)where exists(select 1 from test2 where test2.id=test1.id)MS SQL Server不支持这样的语法,相对应的写法为:update test1set t