ORA-30926错误求助：跨库MERGE更新脚本运行异常

错误原因 #

这个错误的核心是源查询（PERSONNEL@ORA12C_TO_POBM的过滤结果）中，存在多组SURNAME、FORENAMES、BIRTH_DATE完全相同但PERSONNEL_ID不同的记录，导致MERGE操作时，目标表VANTAGEDB.CERT_UPLOAD的同一行匹配到源表的多条数据，数据库无法确定使用哪条PERSONNEL_ID执行更新。

解决方法 #

方法一：对源数据去重，保留唯一匹配记录 #

如果同一组SURNAME+FORENAMES+BIRTH_DATE对应的PERSONNEL_ID是唯一的，仅存在数据重复，可直接用DISTINCT去重：

MERGE INTO VANTAGEDB.CERT_UPLOAD A
     USING (
              SELECT DISTINCT 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;

如果同一组匹配键对应多个不同的PERSONNEL_ID，需明确保留规则（比如取最新记录，假设表有UPDATE_DATE字段）：

MERGE INTO VANTAGEDB.CERT_UPLOAD A
     USING (
              SELECT PERSONNEL_ID,
                     SURNAME,
                     FORENAMES,
                     BIRTH_DATE
                FROM (
                      SELECT PERSONNEL_ID,
                             SURNAME,
                             FORENAMES,
                             BIRTH_DATE,
                             ROW_NUMBER() OVER (PARTITION BY SURNAME, FORENAMES, BIRTH_DATE ORDER BY UPDATE_DATE DESC) AS rn
                        FROM PERSONNEL@ORA12C_TO_POBM
                       WHERE REMARK LIKE 'PORTAL_VANTAGE%'
                     )
                 WHERE rn = 1
           ) 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;

方法二：检查匹配键的唯一性 #

确认目标表VANTAGEDB.CERT_UPLOAD中SURNAME+FORENAMES+BIRTH_DATE是否为唯一键，如果不是，目标表本身的重复行也会加剧匹配冲突。业务允许的情况下，可先清理目标表重复数据，或添加唯一约束。

方法三：改用UPDATE语句关联去重后的源表 #

如果MERGE的去重逻辑不好处理，可直接使用UPDATE结合子查询，确保每个目标行仅匹配一个源行：

UPDATE VANTAGEDB.CERT_UPLOAD A
   SET A.PERSONNEL_ID = (
                          SELECT B.PERSONNEL_ID
                            FROM (
                                  SELECT PERSONNEL_ID,
                                         SURNAME,
                                         FORENAMES,
                                         BIRTH_DATE,
                                         ROW_NUMBER() OVER (PARTITION BY SURNAME, FORENAMES, BIRTH_DATE ORDER BY PERSONNEL_ID) AS rn
                                    FROM PERSONNEL@ORA12C_TO_POBM
                                   WHERE REMARK LIKE 'PORTAL_VANTAGE%'
                                 ) B
                           WHERE     A.SURNAME = B.SURNAME
                                 AND A.FORENAMES = B.FORENAMES
                                 AND A.BIRTH_DATE = B.BIRTH_DATE
                                 AND B.rn = 1
                        )
 WHERE     NOT REGEXP_LIKE (A.PERSONNEL_ID, '^[[:digit:]]+$')
       AND A.CERT_STATUS IS NULL
       AND EXISTS (
                   SELECT 1
                     FROM PERSONNEL@ORA12C_TO_POBM B
                    WHERE     A.SURNAME = B.SURNAME
                          AND A.FORENAMES = B.FORENAMES
                          AND A.BIRTH_DATE = B.BIRTH_DATE
                          AND B.REMARK LIKE 'PORTAL_VANTAGE%'
                  );

内容的提问来源于stack exchange，提问作者POB SISO