The problem is you have many rows in source for each row in target:
create table source (id number, old_val varchar2(20), new_val varchar2(20));
create table target (id number primary key, val varchar2(30));
insert into target values (1,'abc,def,ghi');
insert into source values (1,'abc','jkl');
insert into source values (1,'ghi','mno');
commit;
select tar.id, src.old_val, src.new_val, tar.val
from source src
join target tar
on tar.id = src.id;
ID OLD_VAL NEW_VAL VAL
1 abc jkl abc,def,ghi
1 ghi mno abc,def,ghi
So Oracle Database doesn't know how to process these when you do an update!
One way around this is to:
- Convert the csv in target.val to rows
- Outer join source to the result of this on old_val
- Use listagg to convert the results back into a single row
with rws as (
select id, level rn, regexp_substr(val,'[^,]+', 1, level) v from target
connect by regexp_substr(val, '[^,]+', 1, level) is not null
)
select r.id, listagg(nvl(new_val, v), ',') within group (order by rn) new_vals
from rws r
left join source s
on s.id = r.id
and r.v = s.old_val
group by r.id;
ID NEW_VALS
1 jkl,def,mno
Once you've got this, you can plug it into a merge:
merge into target t
using (
with rws as (
select id, level rn, regexp_substr(val,'[^,]+', 1, level) v from target
connect by regexp_substr(val, '[^,]+', 1, level) is not null
)
select r.id, listagg(nvl(new_val, v), ',') within group (order by rn) new_vals
from rws r
left join source s
on s.id = r.id
and r.v = s.old_val
group by r.id
) src
on (src.id = t.id)
when matched then update
set t.val = src.new_vals;
select * from target;
ID VAL
1 jkl,def,mno