哦,这个逻辑啊,晕 那就是: update A a set a.name=(select B.name from B where B.type=substr(a.code,1,4)), a.type=(select B.type from B where B.type=substr(a.code,1,4));
update A a set a.name=(select max(B.name) from B where B.type=substr(a.code,1,4)), a.type=(select max(B.type) from B where B.type=substr(a.code,1,4));确保唯一对应,才可以更新。
那说明你B里的记录不唯一,而且可能有name为空的,你要先处理B的数据或者如楼上那样
update A a set (name,type)=(select name,type from B where B.type=substr(a.code,1,4));
各位大侠,你们定的SQL测试过吗?谢谢各位了
update A a set (name,type)=(select nvl(max(name),'null'),max(type) from B where B.type=substr(a.code,1,4));
你的语句中 a.type=B.type ,但a.type是空值,最终不能更新
那就是:
update A a
set a.name=(select B.name from B where B.type=substr(a.code,1,4)),
a.type=(select B.type from B where B.type=substr(a.code,1,4));
(Error): ORA-01407: 无法更新 ("ZHENGGUAN"."A"."NAME") 为 NULL
set a.name=(select max(B.name) from B where B.type=substr(a.code,1,4)),
a.type=(select max(B.type) from B where B.type=substr(a.code,1,4));确保唯一对应,才可以更新。
(name,type)=(select name,type from B
where B.type=substr(a.code,1,4));
(name,type)=(select nvl(max(name),'null'),max(type) from B
where B.type=substr(a.code,1,4));