A表
operatename modelid messageid version
- 1100 101 1
- 1100 102 1
- 1100 103 1
B表
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 a,b
where a.modelid=b.modelid
and a.messageid=b.messageid
and a.version=b.version
where exists(select 1
from a,b
where a.modelid=b.modelid
and a.messageid=b.messageid
and a.version=b.version)
但是提示:单行子查询返回多于一行
我想要逐行更新表A的话,子查询肯定返回三行了,
这个SQL语句该怎么写呢
7 个解决方案
#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)
set operatename=(select b.operatename
from b
where a.modelid=b.modelid
and a.messageid=b.messageid
and a.version=b.version)
#2
update a
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);
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);
#3
update a set operatename=(select b.operatename 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)
但要必须保证一一对应。
#4
谢谢 welyngj(平平淡淡) ,
谢谢大家。现在好象好了,下午结帖!!!!
谢谢大家。现在好象好了,下午结帖!!!!
#5
A表
operatename modelid messageid version
- 1100 101 1
- 1100 102 1
- 1100 103 1
ddd 1100 105 1
B表
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表中存在的记录,其他记录的值保持不变呢?
operatename modelid messageid version
- 1100 101 1
- 1100 102 1
- 1100 103 1
ddd 1100 105 1
B表
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表中存在的记录,其他记录的值保持不变呢?
#6
have a try:
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) oper
from b right join a on a.modelid=b.modelid
and a.messageid=b.messageid
and a.version=b.version)
#7
少了end
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)
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)
#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)
set operatename=(select b.operatename
from b
where a.modelid=b.modelid
and a.messageid=b.messageid
and a.version=b.version)
#2
update a
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);
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);
#3
update a set operatename=(select b.operatename 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)
但要必须保证一一对应。
#4
谢谢 welyngj(平平淡淡) ,
谢谢大家。现在好象好了,下午结帖!!!!
谢谢大家。现在好象好了,下午结帖!!!!
#5
A表
operatename modelid messageid version
- 1100 101 1
- 1100 102 1
- 1100 103 1
ddd 1100 105 1
B表
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表中存在的记录,其他记录的值保持不变呢?
operatename modelid messageid version
- 1100 101 1
- 1100 102 1
- 1100 103 1
ddd 1100 105 1
B表
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表中存在的记录,其他记录的值保持不变呢?
#6
have a try:
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) oper
from b right join a on a.modelid=b.modelid
and a.messageid=b.messageid
and a.version=b.version)
#7
少了end
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)
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)