问一个简单问题：如何一次性修改多条记录？修改的值从其他表来

[demonwinwin]

类似这种的
insert into tablename1 (select * from tablename2)
有没有
update tablename set (select ...)
或者是
update tablename set a=(select a from tablename2 where tablename2.a=tablename.a)

应该怎么写
求大家帮帮忙？

[guangli_zhang]

update tablename set a=(select a from tablename2 where tablename2.a=tablename.a)
where exists(select 1 from tablename2 where tablename2.a=tablename.a)

[tony817991]

update tablename set a=(select a from tablename2 where tablename2.b=tablename.b)
where tablename.b in (select b from tablename2)

[liuyann]

update tablename
set a=(select max(a) from tablename2 where tablename2.a=tablename.a)

[china_ren5160]

update tablename set (col1,col2,...,coln) = (select (col1,col2,...,coln) from tablename2 where tablename2.a=tablename.a)

[fzyer000]

以上的方法都可以。

[sfbird]

可以使用merge
for example;
merge into a
using (select * from B)b
on (same as where clause: a.name =b.name and a.year = b.year)
when matched then
(update set a.class = b.class)
when not matched then
insert(a.name,a.year,a.class) values (b.name,b.year,b.class)
根据你的需要,可以操作多条记录(修改或添加)^_^
good luck

[demonwinwin]

好像都不行呢，说什么null值无法更新

[waterfirer]

把你的sql贴出来吧，上面的办法肯定是可行的。

[demonwinwin]

应该可以
可能是我有些字段是不容许NULL的
所以有错误