update a
set operatename=(select b.operatename
from b
where a.modelid=b.modelid
and a.messageid=b.messageid
and a.version=b.version)
set operatename=(select b.operatename
from b
where a.modelid=b.modelid
and a.messageid=b.messageid
and a.version=b.version)
set operatename=(select b.operatename
from b
where a.modelid=b.modelid
and a.messageid=b.messageid
and a.version=b.version)
where exist(select 1 from from b
where a.modelid=b.modelid
and a.messageid=b.messageid
and a.version=b.version);
and a.messageid=b.messageid and a.version=b.version)
但要必须保证一一对应。
谢谢大家。现在好象好了,下午结帖!!!!
operatename modelid messageid version
- 1100 101 1
- 1100 102 1
- 1100 103 1
ddd 1100 105 1B表
operatename modelid messageid version
aaa 1100 101 1
bbb 1100 102 1
ccc 1100 103 1
ddd 1100 104 1要用一个UPDATE语句把表A UPDATE 成
operatename modelid messageid version
aaa 1100 101 1
bbb 1100 102 1
ccc 1100 103 1在这种情况下再执行:
update a
set operatename=(select b.operatename
from b
where a.modelid=b.modelid
and a.messageid=b.messageid
and a.version=b.version)
会把 A表的第四条记录中operatename字段的值 'ddd' update 成空值
A,B表的关键字段是 modelid,messageid,version,
该怎样限定条件,只是update A表中对应在B表中存在的记录,其他记录的值保持不变呢?
update a
set operatename=(select (case when b.modelid is null then a.operatename
else b.operatename) oper
from b right join a on a.modelid=b.modelid
and a.messageid=b.messageid
and a.version=b.version)
update a
set operatename=(select (case when b.modelid is null then a.operatename
else b.operatename end) oper
from b right join a on a.modelid=b.modelid
and a.messageid=b.messageid
and a.version=b.version)