MERGE语句触发ORA-30926：含NULL值的ACC_NO匹配失败

问题场景与现象 #

• 需求：将SOURCE_INPUT表中非空的BATCH_NUMBER和RECORD_NUMBER合并到TARGET_TABLE中
• 场景：两张表各存在2条ACC_NO为NULL的记录，其余记录ACC_NO均有有效值
• 问题：使用NVL(TAR.ACC_NO,'NULL') = NVL(MRG.ACC_NO,'NULL')处理NULL值匹配时，即便添加了DISTINCT和ORDER BY，仍触发ORA-30926“无法在源表中获得稳定的行集”错误；仅匹配非空ACC_NO的MERGE语句可正常运行

触发错误的MERGE代码 #

MERGE INTO TARGET_TABLE TAR
USING (
          SELECT
              DISTINCT(INP.BATCH_NUMBER),
              INP.RECORD_NUMBER,
              INP.ACC_NO
          FROM
              SOURCE_INPUT INP
              ORDER BY INP.BATCH_NUMBER
      )MRG 
      ON ( NVL(TAR.ACC_NO,'NULL') = NVL(MRG.ACC_NO,'NULL') )
WHEN MATCHED THEN UPDATE
SET TAR.BATCH_NUMBER = MRG.BATCH_NUMBER,
    TAR.RECORD_NUMBER = MRG.RECORD_NUMBER;

正常运行的MERGE代码（仅匹配非空ACC_NO） #

MERGE INTO TARGET_TABLE TAR
USING (
          SELECT
              INP.BATCH_NUMBER,
              INP.RECORD_NUMBER,
              INP.ACC_NO
          FROM
              SOURCE_INPUT INP
      )MRG 
      ON TAR.ACC_NO = MRG.ACC_NO
WHEN MATCHED THEN UPDATE
SET TAR.BATCH_NUMBER = MRG.BATCH_NUMBER,
    TAR.RECORD_NUMBER = MRG.RECORD_NUMBER;

错误原因 #

ORA-30926的核心触发条件是源表中存在多条记录匹配目标表的同一条记录，Oracle无法确定用哪条源记录执行更新操作。
使用NVL处理NULL后，所有ACC_NO为NULL的源记录会匹配所有ACC_NO为NULL的目标记录，形成多对多的匹配关系。即便添加DISTINCT，如果源表中ACC_NO为NULL的记录有不同的BATCH_NUMBER/RECORD_NUMBER，去重后仍会有多条源记录对应目标的NULL行，依然触发错误；ORDER BY仅影响排序，无法解决行集唯一性问题。

解决方案 #

方案1：为每个匹配组指定唯一源记录 #

通过窗口函数为ACC_NO相同的分组（包括NULL）生成唯一行号，每组仅保留一条记录作为更新源，确保匹配关系为一对一：

MERGE INTO TARGET_TABLE TAR
USING (
          SELECT
              INP.BATCH_NUMBER,
              INP.RECORD_NUMBER,
              INP.ACC_NO
          FROM (
              SELECT 
                  INP.*,
                  -- 按ACC_NO分组（含NULL），取BATCH_NUMBER最新的一条记录
                  ROW_NUMBER() OVER (PARTITION BY NVL(INP.ACC_NO, 'NULL') ORDER BY INP.BATCH_NUMBER DESC) AS rn
              FROM SOURCE_INPUT INP
              WHERE INP.BATCH_NUMBER IS NOT NULL AND INP.RECORD_NUMBER IS NOT NULL
          ) INP
          WHERE rn = 1
      )MRG 
ON ( NVL(TAR.ACC_NO, 'NULL') = NVL(MRG.ACC_NO, 'NULL') )
WHEN MATCHED THEN UPDATE
SET TAR.BATCH_NUMBER = MRG.BATCH_NUMBER,
    TAR.RECORD_NUMBER = MRG.RECORD_NUMBER;

方案2：拆分NULL与非NULL的更新逻辑 #

分开处理两种场景，避免多对多匹配：

-- 处理ACC_NO非空的记录，复用原有正常逻辑
MERGE INTO TARGET_TABLE TAR
USING (
          SELECT
              INP.BATCH_NUMBER,
              INP.RECORD_NUMBER,
              INP.ACC_NO
          FROM SOURCE_INPUT INP
          WHERE INP.ACC_NO IS NOT NULL
            AND INP.BATCH_NUMBER IS NOT NULL AND INP.RECORD_NUMBER IS NOT NULL
      )MRG 
ON TAR.ACC_NO = MRG.ACC_NO
WHEN MATCHED THEN UPDATE
SET TAR.BATCH_NUMBER = MRG.BATCH_NUMBER,
    TAR.RECORD_NUMBER = MRG.RECORD_NUMBER;

-- 处理ACC_NO为NULL的记录，明确指定更新用的源记录（示例取BATCH_NUMBER最大的）
UPDATE TARGET_TABLE TAR
SET (TAR.BATCH_NUMBER, TAR.RECORD_NUMBER) = (
    SELECT INP.BATCH_NUMBER, INP.RECORD_NUMBER
    FROM SOURCE_INPUT INP
    WHERE INP.ACC_NO IS NULL
      AND INP.BATCH_NUMBER IS NOT NULL AND INP.RECORD_NUMBER IS NOT NULL
    ORDER BY INP.BATCH_NUMBER DESC
    FETCH FIRST 1 ROW ONLY
)
WHERE TAR.ACC_NO IS NULL;

方案3：用IS NOT DISTINCT FROM简化NULL匹配（Oracle 12cR1+） #

Oracle 12cR1及以上支持IS NOT DISTINCT FROM运算符，可直接将NULL与NULL视为相等，同时配合窗口函数保证源行唯一性：

MERGE INTO TARGET_TABLE TAR
USING (
          SELECT
              INP.BATCH_NUMBER,
              INP.RECORD_NUMBER,
              INP.ACC_NO
          FROM (
              SELECT 
                  INP.*,
                  ROW_NUMBER() OVER (PARTITION BY INP.ACC_NO ORDER BY INP.BATCH_NUMBER DESC) AS rn
              FROM SOURCE_INPUT INP
              WHERE INP.BATCH_NUMBER IS NOT NULL AND INP.RECORD_NUMBER IS NOT NULL
          ) INP
          WHERE rn = 1
      )MRG 
ON (TAR.ACC_NO IS NOT DISTINCT FROM MRG.ACC_NO)
WHEN MATCHED THEN UPDATE
SET TAR.BATCH_NUMBER = MRG.BATCH_NUMBER,
    TAR.RECORD_NUMBER = MRG.RECORD_NUMBER;

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