create table dest 
(id number primary key,
 col1 varchar2(10));

create table src
(id number,
 col1 varchar2(10));

insert into src values(1,'ABC');
insert into src values(2,'GHB');
insert into src values(3,'DUP');
insert into src values(3,'DUP');

commit;

merge into dest 
  using 
  (select id,col1 from src) src on(dest.id=src.id)
when not matched then 
  insert values(src.id,src.col1)
when matched 
  then update set dest.col1=src.col1;

it would be non-deterministic if it did that.

That is, given the same set of input data - we could come up with different "answers" depending on how the rows were accidentally processed.

One day we might insert "row A" and then update it with "row B"

Next day we might insert "row B" and then update it with "row A"

same inputs exactly, different answers.


Merge uses read consistency to process, the set of rows that can be modified is "set in stone" as of the time the statement begins processing.


You would need to use distinct on the "using" set of data in this case.