By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table employee (id, name) as (
select '001', 'A' from dual union all
select '002', 'X' from dual );
2 rows affected
create table employee_account_ids(emp_id, acc_id) as (
select '001', 'ACC05' from dual union all
select '001', 'ACC13' from dual union all
select '002', 'ACC52' from dual );
3 rows affected
select * from employee_account_ids;
| EMP_ID | ACC_ID |
|---|---|
| 001 | ACC05 |
| 001 | ACC13 |
| 002 | ACC52 |
merge into employee_account_ids tgt
using (
with new_data(acc_id) as (select * from table(sys.odcivarchar2list('NEW01', 'NEW02', 'ACC13')))
select '001' emp_id, nvl(a.acc_id, n.acc_id) acc_id, rwd,
case when n.acc_id is null then 'del' end dsc
from new_data n
full join (select e.*, rowid rwd from employee_account_ids e where emp_id = '001') a
on a.acc_id = n.acc_id ) src
on (src.emp_id = tgt.emp_id and src.rwd = tgt.rowid)
when matched then update set tgt.acc_id = tgt.acc_id delete where dsc = 'del'
when not matched then insert values (src.emp_id, src.acc_id);
4 rows affected
select * from employee_account_ids;
| EMP_ID | ACC_ID |
|---|---|
| 001 | ACC13 |
| 002 | ACC52 |
| 001 | NEW01 |
| 001 | NEW02 |