update fin_com_compare a
set a.center_code = (select b.item_code
from fin_com_siitem b
where b.name = a.center_name
and a.center_code = 'Test'
and a.center_name is not null)----上面的sql执行的时候报错 ora-01407 无法更新center_code为Null
where exists (select 1 from fin_com_siitem b where b.name = a.center_name)
and a.center_code = 'Test'
and a.center_name is not null--加上where条件后 报错 ora-01427 单个子查询返回多个行 什么原因啊。实际上我的意思就是如果 b表的name与a表的center_name相同的话 将b表的item_code更新到a表的center_code
set a.center_code = (select b.item_code
from fin_com_siitem b
where b.name = a.center_name
and a.center_code = 'Test'
and a.center_name is not null)----上面的sql执行的时候报错 ora-01407 无法更新center_code为Null
where exists (select 1 from fin_com_siitem b where b.name = a.center_name)
and a.center_code = 'Test'
and a.center_name is not null--加上where条件后 报错 ora-01427 单个子查询返回多个行 什么原因啊。实际上我的意思就是如果 b表的name与a表的center_name相同的话 将b表的item_code更新到a表的center_code
from fin_com_siitem b
where b.name = a.center_name
and a.center_code = 'Test'
and a.center_name is not null这个返回值唯一么?
应该是 1 a
2 b
3 c 这样的就可以
如果是有 1 a
1 b
或者
1 a
2 a
就不行了。
update fin_com_compare a
set a.center_code = (select b.item_code
from (select distinct name,item_code from fin_com_siitem ) b
where exists (select 1 from b where b.name = a.center_name)
and a.center_code = 'Test'
and a.center_name is not null
)
merge into fin_com_compare a
using fin_com_siitem b
on( b.name = a.center_name)
when matched then
update set a.center_code = b.item_code
where a.center_code = 'Test'
and a.center_name is not null;
b表里面的是唯一的的 例如 1 a ,2 b .....
原因就是name有重复的。