ORA-30926: unable to get a stable set of rows in the source tables when updating a a table in a database using another table in another database

Question

I am trying to update a table in a database using another table in another database. This script was working fine until recently when I started getting the error: ORA-30926: unable to get a stable set of rows in the source tables.

Please find the Script below.

MERGE INTO VANTAGEDB.CERT_UPLOAD A
     USING (
              SELECT PERSONNEL_ID,
                     SURNAME,
                     FORENAMES,
                     BIRTH_DATE
                FROM PERSONNEL@ORA12C_TO_POBM
               WHERE REMARK LIKE 'PORTAL_VANTAGE%'
           ) B
        ON (    A.SURNAME = B.SURNAME
            AND A.FORENAMES = B.FORENAMES
            AND A.BIRTH_DATE = B.BIRTH_DATE)
WHEN MATCHED
THEN
   UPDATE SET A.PERSONNEL_ID = B.PERSONNEL_ID
           WHERE     NOT REGEXP_LIKE (PERSONNEL_ID, '^[[:digit:]]+$')
                 AND CERT_STATUS IS NULL;

I have run these embedded scripts alone and they worked fine. What do i do please?

Answer 1

(This can't fit into a comment and I can't format code there).

Apparently,

select surname, forename, birth_date 
from personnel 
group by surname, forename, birth_date 
having count(*) > 1;

returns some duplicates.

That's the cause of your problems because you're using these 3 columns while joining target table (CERT_UPLOAD) to source data set (subquery). As there are duplicates, Oracle doesn't know which PERSONNEL_ID to take.

For example, if cert_upload contains

surname forename birth_date personnel_id
------- -------- ---------- ------------
White   Mike     10.01.2020       (NULL)

and subquery returned

surname forename birth_date personnel_id
------- -------- ---------- ------------ 
White   Mike     10.01.2020          100
White   Mike     10.01.2020          992

which personnel_id (100 or 992) should be put into cert_upload table? I don't know, Oracle doesn't know it either.

It means that you'll have to include additional column(s) to uniquely identify each row so that join would be 1:1.