Columns referenced in the ON Clause cannot be updated Oracle

Question

I am trying to update a column in a table using the value from another table. I am using table b to get my id's and joining to table a, this then has a dc_id and i am updating table c using that dc_id and the population_info_id.

But when using merge i get the error "Columns referenced in the ON Clause cannot be updated", but i need to use the population_info_id to identify the correct record. How can i get around this?

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;

Answer 1

There's some good options in this similar question. One of them is that you can move that condition out of the on clause into a where clause:

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 ) -- from here
WHEN MATCHED THEN UPDATE
SET dc.population_info_id = src.new_population_info_id
WHERE dc.population_info_id = src.loaded_pii; -- to here

This could have some performance impacts. If that's not acceptable, consider trying one of the "renaming tricks" from those answers.

In your case, you can also do this with a plain update statement, but it's not exactly graceful since you need to duplicate the src subquery.

update table_c dc
set dc.population_info_id = (select 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
    where dc.dc_id = a.dc_id
      and dc.population_info_id = b.loaded_pii
    )
where exists (select 1 from table_a a
      INNER JOIN table_b   b 
      ON a.column_id= b.column_id
    where dc.dc_id = a.dc_id
      and dc.population_info_id = b.loaded_pii)