SQL Update的四种常见写法
SQL Update的四种常见写法:Oralce和DB2都支持的语法:update test1set (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 SQ
·
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 test1
set test1.name=test2.name,test1.age=test2.age
from test1
inner join 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 inner join 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)
更多推荐
已为社区贡献3条内容
所有评论(0)