My Oracle Support Banner

Merge Statement With Unqualified Ambiguous Columns Does Not Raise ORA-918 And Updates No Rows (Doc ID 1910098.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

Symptoms

A merge command doesn't work when data exists in both the source and the target tables as in the following test:

create table source(c1 number, c2 varchar2(10));
create table target(c1 number, c2 varchar2(10));
insert into source values(1, '1');
commit;

 

>>>>>>>>>>>>>>> Table created.
>>>>>>>>>>>>>>> Table created.
>>>>>>>>>>>>>>> 1 row created.
>>>>>>>>>>>>>>> Commit complete.


merge into target t
using source s
on (t.c1 = s.c1)
when matched then
update set c2 = s.c2
where c2 <> s.c2
when not matched then
insert values(s.c1, s.c2);

 

>>>>>>>>>>>>>>> 1 row merged.

Now data exists in both tables. 

update source set c2 = '12' where c1 = 1;

 

>>>>>>>>>>>>>>> 1 row updated.


merge into target t
using source s
on (t.c1 = s.c1)
when matched then
update set c2 = s.c2
where c2 <> s.c2
when not matched then
insert values(s.c1, s.c2);

 

>>>>>>>>>>>>>>> 0 row merged.

Notice that the merge does not qualify "c2" in the update/where clauses.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.