MERGE oddity

Here’s a little observation on some slightly odd behaviour with MERGE and some apparent inconsistency of error ORA-30926 (prompted by a question on the otn forums).

If you’re not familar with ORA-30926:

ORA-30926: unable to get a stable set of rows in the source tables

then this is expected with MERGE if the set of SOURCE rows (identified in the USING clause) that you’re merging into the TARGET (i.e. the MERGE INTO <target>) contain duplicate entries for the key columns concerned, i.e. if there are duplicates, which one do you want to be preserved by the MERGE.

Setup:

SQL> create table a
  2  (key number
  3  ,val varchar2(1));

Table created.

SQL> 
SQL> create table b
  2  (key number
  3  ,val varchar2(1));

Table created.

SQL> 
SQL> insert into a values(1,'A');

1 row created.

SQL> 
SQL> insert into b values(1,1);

1 row created.

SQL> 
SQL> insert into b values(1,2);

1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> select * from b;

       KEY V
---------- -
         1 1
         1 2
SQL> 

We might expect ORA-30926 to be raised in this next example, but it’s not:

SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> 
SQL> merge into a
  2  using (select b.val
  3         ,      b.key
  4         from   b) b
  5  on (a.key = b.key)
  6  when matched then 
  7   update
  8   set a.val = decode(b.val,'1','A','B');

2 rows merged.

SQL> 
SQL> select * from a;

       KEY V
---------- -
         1 B

SQL> 

But if we run it again:

SQL> select * from a;

       KEY V
---------- -
         1 B

SQL> merge into a
  2  using (select b.val
  3         ,      b.key
  4         from   b) b
  5  on (a.key = b.key)
  6  when matched then 
  7   update
  8   set a.val = decode(b.val,'1','A','B');
merge into a
           *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables



SQL> rollback;

Rollback complete.

SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> 

If we change this slightly by adding and altering an ORDER BY:

SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> merge into a
  2  using (select b.val
  3         ,      b.key
  4         from   b
  5         order by b.val ASC) b  --<----- ORDER BY   
  6  on (a.key = b.key)
  7  when matched then 
  8   update
  9   set a.val = decode(b.val,'1','A','B');

2 rows merged.

SQL> select * from a;

       KEY V
---------- -
         1 B

SQL> merge into a
  2  using (select b.val
  3         ,      b.key
  4         from   b
  5         order by b.val DESC) b  --<----- ORDER BY   
  6  on (a.key = b.key)
  7  when matched then 
  8   update
  9   set a.val = decode(b.val,'1','A','B');

2 rows merged.

SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> 

Perhaps we get an insight into how the check that results in ORA-30926 is implemented?

It seems to be not as simple as a question of whether there are duplicates in the SOURCE but whether the MERGE would update the same row twice – i.e. an UPDATE that results in an UPDATE not just updating it to the existing value.

The bottom line is that you should never be merging in multiple updates to the same target row.
If there are such incoming data, you have to have logic to filter that down.