Hi All,
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
PL/SQL Release 12.1.0.1.0 - Production 0
CORE 12.1.0.1.0 Production 0
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production 0
NLSRTL Version 12.1.0.1.0 - Production 0
When using MERGE clause, if executed more than one time, I am getting the below error
ORA-30926: unable to get a stable set of rows in the source tables
create table src ( snum number, sname varchar2(10))
create table tar (tnum number, tname varchar2(10))
insert into src values(1, 'a')
insert into src values(1, 'b')
insert into src values(2, 'c')
merge into tar
using src
on (snum=tnum)
when matched
then
update set tname=sname
when not matched
then
insert values(snum,sname);
What am expecting is that first time itself (When inserting) it should throw the error. But it is not.
select * from tar
TNUM TNAME
1 b
1 a
2 c
But if we executing the second time it is showing the error.
I have seen Oracle document and it says
MERGE is a deterministic statement. You cannot update the same row of the target table multiple times in the same MERGE statement.
I have seen this below threads but unable to get the solution for this.
Anyone clarify this?
I don't want to insert those records in first time itself. i.e., running first time it should throw the error. Can i achieve this using MERGE?
Thanks