1.问题描述
merge into执行更新时无法更新on中的字段
2.错误提示
ORA-38104:无法更新on子句中引用的列
3.merge into语法
merge into target_table a
using source_table b
on(a.condition1=b.condition1 and a.condition2=b.condition2 ……)
when matched then update set a.field=b.field,....
when not matched then insert into a(field1,field2……)values(value1,value12……)
示例:
merge into CIF_PERBASEINFO a
using (select '37*********X' cardnum from dual ) b
on (a.CARDNUM=b.cardnum )
when matched then update set cname='更新'
when not matched then insert (cardnum,CNAME) values ( '31**********1' ,'新增');
4.更新on字段中的问题,由于
merge into CIF_PERBASEINFO a
using (select '37*********X' cardnum from dual ) b
on (a.CARDNUM=b.cardnum and a.ISALLCREDIT is null )
when matched then update set cname='客户类型1', ISALLCREDIT='A01'
when not matched then insert (cardnum,cname,ISALLCREDIT) values ('37*********X', '客户类型2','A02' )
5.解决办法: on里的条件放到update 之后的where条件里
merge into CIF_PERBASEINFO a
using (select '37*********X' cardnum
from dual) b
on (a.CARDNUM = b.cardnum )
when matched then
update
set cname='客户类型1',
ISALLCREDIT='A01' where a.ISALLCREDIT is null
when not matched then
insert (cardnum,cname, ISALLCREDIT)
values ('37*********X','客户类型2', 'A02')