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.
create table test_tab_A
(col1_num number(10),
col2_dat date);
(col1_num number(10),
col2_dat date);
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);
COL1_NUM COL2_DAT
---------- ---------
1 04-JUN-12
1 04-JUN-12
1 04-JUN-12
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
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)
ORA-30926: unable to get a stable set of rows in the source tables
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;
---------- ---------
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;
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
