Hi all,
I have a merge statement that sometimes fails when the source table has duplicated merge keys.
To save time I tried to use dbms_errlog package and let it save the coulript rows, without failing the statement itself.

The error I get before using dbms_errlog is selfspeaking:

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

Using the mentioned package the merge is successful.
My problem is that I realised that even the target row with more than one corresponding row in the source table is being updated.
I would have expected that the statement only updated the target rows with a single corresponding record on source, skipping and logging the error in the other cases.

this is the test script

<--test dml error logging

-- target table
drop table dest_testlog;
create table dest_testlog (c1 number, c2 varchar2(20));
insert into dest_testlog
select level , 'DEST'   from dual connect by level <= 5;

--source table
drop table src_testlog2 ;
create table src_testlog2(c1 number, c2 varchar2(20));
insert into src_testlog2
select level c1, 'SOURCE' as c2 from dual connect by level <= 5;

-- duplicate key on source table
update src_testlog2 set c1 = 1, c2 = '????'
where c1 = 2;
select * from src_testlog2 ;

-- ORA-30926 error:  unable to get a stable set of rows in the source tables
merge into dest_testlog l
using src_testlog2 ll
on (l.c1 = ll.c1)
when matched then update set
 l.c2 = ll.c2;
   
-- create error log table
drop table err$_dest_testlog;
begin
   dbms_errlog.create_error_log(dml_table_name => 'dest_testlog');
end;
/

select * from err$_dest_testlog;

-- retry merge with log errors into clause....
-- merge is now successful, 
merge into dest_testlog l
using src_testlog2 ll
on (l.c1 = ll.c1)
when matched then update set
 l.c2 = ll.c2
log errors into err$_dest_testlog ('have a look!!!') reject limit unlimited;

-- check log for errors errore
select * from err$_dest_testlog;

select * from dest_testlog where rowid in (select elog.ora_err_rowid$ from err$_dest_testlog elog);
select * from dest_testlog;

C1 C2
1 ????   <<-- this was not desired, should be left "DEST" instead.
2 DEST
3 SOURCE
4 SOURCE
5 SOURCE


--- WOW!! update has been done even for row with unstable source dataset

--drop table dest_testlog;
--drop table src_testlog2;
---drop table err$_dest_testlog;

Is there a way to avoid this behaviour and make merge only update the rows that can be safely (i.e. unambiguously) update?

Thanks
MatteoP

merge into dest_testlog l
using (select c1, c2, 
              count(*) over (partition by c1) ct 
       from   src_testlog2
      ) ll
on (l.c1 = ll.c1)
when matched then update set
 l.c2 = ll.c2
 where  ct < 2;

select * from dest_testlog;

C1   C2       
   1 DEST     
   2 DEST     
   3 SOURCE   
   4 SOURCE   
   5 SOURCE

rollback;

delete dest_testlog
where  c1 = 1;

select * from dest_testlog;

C1   C2     
   2 DEST   
   3 DEST   
   4 DEST   
   5 DEST 

merge into dest_testlog l
using (select c1, c2, 
              count(*) over (partition by c1) ct 
       from   src_testlog2
      ) ll
on (l.c1 = ll.c1)
when matched then update set
 l.c2 = ll.c2
 where  ct < 2
when not matched then insert values (ll.c1, ll.c2);

select * from dest_testlog;

C1   C2       
   2 DEST     
   3 SOURCE   
   4 SOURCE   
   5 SOURCE   
   1 SOURCE   
   1 ????