Oracle merge – unable to get stable set of rows after deleting duplicates

I have this query to merge data from the individuals2 table. I still get the “unable to get a stable set of rows” error after deleting all the full_name duplicates in individuals2. What am I missing?

MERGE INTO TBLHISTORYPERSON h
USING individuals2 i
    ON (lower(i.FULL_NAME) = lower(h.FULL_NAME) AND 
        i.DOB = h.hDOB)
WHEN MATCHED THEN UPDATE 
SET h.PERSON_ID = i.PERSON_ID
WHERE h.PERSON_ID IS null

Answer

Your problem might be in the lower(FULL_NAME)expression in the join condition.

It is not enough that FULL_NAME and DOB are unique in the table individuals2.

lower(FULL_NAME) and DOB must be unique

A small example

create table TBLHISTORYPERSON
(FULL_NAME varchar2(10),
hdob int,
person_id int);

insert into TBLHISTORYPERSON (FULL_NAME, hdob, person_id)
values ('Max',1,null);

create table individuals2
(FULL_NAME varchar2(10),
dob int,
person_id int);

insert into individuals2 (FULL_NAME, dob, person_id)
values ('Max',1,2);
insert into individuals2 (FULL_NAME, dob, person_id)
values ('max',1,3);


MERGE INTO TBLHISTORYPERSON h
USING individuals2 i
    ON (lower(i.FULL_NAME) = lower(h.FULL_NAME) AND 
        i.DOB = h.hDOB)
WHEN MATCHED THEN UPDATE 
SET h.PERSON_ID = i.PERSON_ID
WHERE h.PERSON_ID IS null
;

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

Remove one of the rows and it works

delete from individuals2 where full_name = 'Max';

1 row deleted.


The MERGE works

1 row merged.

Select * from TBLHISTORYPERSON;

FULL_NAME        HDOB  PERSON_ID
---------- ---------- ----------
Max                 1          3