求助一条sql语句

cqlhc1 发布于 2013-12-23 14:55， 627 次点击

表1
mo   st
1    a
2    b
3    c
表2
mo   st
1    aa
2    bb
3    cc
4    dd
注意"表2"要多一行
现在需要通过mo关系，把表1的st更新到表2的st里，但表2里多的行(也就是表1里没有的mo)更新为'0'
只用一条语句，能行吗，怎么写.
谢谢!

5 回复

cqlhc12013-12-24 11:23

update t2 set t2.st=case when not exists (select * from t1 where t2.mo=t1.mo) then '0'
else
(select st from t1 where t1.mo=t2.mo)
end
这样写好像可以

cqlhc12013-12-24 11:31

update a set a.st= case
when b.mo=a.mo then b.st
else 0
end
from t1 b right join t2 a on b.mo=a.mo
这样写也行

qw11617485792013-12-25 09:54

update t2 set st = (case when t2.mo in (select mo from t1) then (select st from t1 where t1.mo = t2.mo) else '0' end)

清风幽闲2014-03-03 14:17

UPDATE TB2 SET TB2.ST=CASE
WHEN TB2.MO=TB1.MO THEN TB1.ST
ELSE '0'
END
FROM TB1 JOIN TB2 ON TB1.MO=TB2.MO

西风独自凉2014-03-04 11:29

update t2 set t2.st=isnull(t1.st,'0') from t2 a left join t1 on t1.mo=a.mo