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

Just thought about posting an answer to a simple question that came up while at work.
One of my colleague asked me, what will be the expected result when we merge a table B into Table A, under the following scenario.

• Table A has no rows.
• Table B has 10 rows but all rows have same data (duplicates)

Will table A end up having 10 rows after the merge or just 1 row since merge statement will find a matching row in table A after the first row is inserted?

 

create table test_tab_A

(col1_num number(10),

col2_dat date);

 

create table test_tab_B

(col1_num number(10),

col2_dat date);

 

insert into test_tab_b

select 1, sysdate from dual

union all

select 1, sysdate from dual

union all

select 1, sysdate from dual

... 

 

merge into test_tab_A AA

using test_tab_B BB

on (AA.col1_num = BB.col1_num)

when matched then

update set aa.col2_dat = bb.col2_dat

when not matched then

insert ( col1_num, col2_dat)

values (BB.col1_num, bb.col2_dat);

 

select * from test_tab_A;

 

COL1_NUM COL2_DAT

---------- ---------

1 04-JUN-12

1 04-JUN-12

1 04-JUN-12

1 04-JUN-12

... 

10 rows selected.

 

We end up having 10 rows because the merge statement takes the point in time image of test_tab_A before the merge statement begin and merge it with test_tab_B.

The point in time before image of test_tab_A had no rows so we end up having 10 rows.

But, If the point in time before image had at least one row matching the merge condition then we would end up having oracle error 30926.

In other words, if more than one row in source table maps to a row single row in the target table then we will be heading into an error condition as below

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


DELETE test_tab_A;


10 rows deleted.


insert into test_tab_A

select 1, sysdate from dual;

 

1 row created.


merge into test_tab_A AA

using test_tab_B BB

on (AA.col1_num = BB.col1_num)

when matched then

update set aa.col2_dat = bb.col2_dat

when not matched then

insert ( col1_num, col2_dat)

values (BB.col1_num, bb.col2_dat)

 

Error at line 62

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

 

To overcome the issue we should take care of mapping a single row from source table to one or more rows in target table.

 

merge into test_tab_A AA

using (select col1_num, MAX(col2_dat) col2_dat

from test_tab_B

group by col1_num) BB

on (AA.col1_num = BB.col1_num)

when matched then

update set aa.col2_dat = bb.col2_dat

when not matched then

insert ( col1_num, col2_dat)

values (BB.col1_num, bb.col2_dat);

 

1 row merged.