Oracle报错：ON子句引用列无法更新的解决方案咨询

需要更新table_c的population_info_id列，逻辑为：通过table_b关联table_a获取dc_id，再结合population_info_id定位目标记录执行更新。但执行MERGE语句时触发报错：Columns referenced in the ON Clause cannot be updated，且必须依赖population_info_id来匹配正确的更新记录，求解决办法。

原执行的MERGE语句：

MERGE INTO table_c dc
USING (
          SELECT /*+ PARALLEL(A,12) PARALLEL(b,12) */
              a.dc_id,
              b.loaded_pii   AS loaded_pii,
              b.true_pii + 1 AS new_population_info_id
          FROM
              table_a           a
              INNER JOIN table_b   b 
              ON a.column_id= b.column_id
            
      )
src 
ON ( dc.dc_id = src.dc_id
        and dc.population_info_id = src.loaded_pii
        )
WHEN MATCHED THEN UPDATE
SET dc.population_info_id = src.new_population_info_id;

Oracle的MERGE语法限制：不能更新ON子句中引用的列，针对这个问题有两种可行方案：

方案1：改用UPDATE+关联子查询 #

直接用UPDATE语句结合子查询匹配目标行，避开MERGE的限制。示例代码：

UPDATE /*+ PARALLEL(dc,12) */ table_c dc
SET dc.population_info_id = (
    SELECT b.true_pii + 1
    FROM table_a a
    JOIN table_b b ON a.column_id = b.column_id
    WHERE a.dc_id = dc.dc_id
      AND b.loaded_pii = dc.population_info_id
)
WHERE EXISTS (
    SELECT 1
    FROM table_a a
    JOIN table_b b ON a.column_id = b.column_id
    WHERE a.dc_id = dc.dc_id
      AND b.loaded_pii = dc.population_info_id
);

• 用EXISTS子句确保只更新有匹配的行，避免无匹配时将列更新为NULL；
• 保留并行提示/*+ PARALLEL(dc,12) */以维持原语句的性能。

方案2：调整MERGE的条件位置（仅适用于dc_id为唯一键的场景） #

如果dc_id在table_c中是唯一标识，可以将population_info_id的匹配条件从ON子句移到WHEN MATCHED的WHERE子句中：

MERGE INTO table_c dc
USING (
    SELECT /*+ PARALLEL(A,12) PARALLEL(b,12) */
        a.dc_id,
        b.loaded_pii AS loaded_pii,
        b.true_pii + 1 AS new_population_info_id
    FROM table_a a
    INNER JOIN table_b b ON a.column_id = b.column_id
) src 
ON (dc.dc_id = src.dc_id)
WHEN MATCHED THEN UPDATE
SET dc.population_info_id = src.new_population_info_id
WHERE dc.population_info_id = src.loaded_pii; -- 原ON中的条件移至此处

• 注意：此方案仅在dc_id是table_c的唯一键时安全，否则会导致同一dc_id下的多行被匹配，可能引发非预期的更新；
• 如果dc_id不唯一，优先使用方案1。

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