Hello Guys
I am facing a problem while executing a merge statement twice per below
Version 12.1.0.2.0
--- CREATE DATA----
create table test_merge ( name varchar2(50), cid number);
insert into test_merge values ('Delaware US',null);
insert into test_merge values ('Delaware Us',null);
--- MERGE-- The below fails, which is correct and acceptable---
merge into test_merge tm
using (select 1 cid, 'Delaware US' name from dual union all
select 1 cid, 'Delaware Us' name from dual) tmp
on (upper(tm.name) = upper(tmp.name))
when matched then update
set cid= tmp.cid
--- Removing upper keyword allows the merge statement to run correctly---
merge into test_merge tm
using (select 1 cid, 'Delaware US' name from dual union all
select 1 cid, 'Delaware Us' name from dual) tmp
on (tm.name = tmp.name)
when matched then update
set cid= tmp.cid
commit;
--- Now again exexuting the first merge statement, Expecting this to fail, but it doesn't-- Why so?
merge into test_merge tm
using (select 1 cid, 'Delaware US' name from dual union all
select 1 cid, 'Delaware Us' name from dual) tmp
on (upper(tm.name) = upper(tmp.name))
when matched then update
set cid= tmp.cid
-- Also if I change just the cid column in the using clause, the merge again fails.
merge into test_merge tm
using (select 1 cid, 'Delaware US' name from dual union all
select 2 cid, 'Delaware Us' name from dual) tmp
on (upper(tm.name) = upper(tmp.name))
when matched then update
set cid= tmp.cid
Please help me understand this behaviour.
Regards