sqloracle-databaseora-38104

ORA-38104: LHS of UPDATE SET contains the columns referenced in the ON Clause


I need to update the rows on (D.SELLER_ACCOUNT_ID = S.ACCOUNT_ID AND D.CATEGORY_ID = S.CATEGORY_ID_OLD).

How is it possible to correct the query below in Oracle?

MERGE INTO T_EVM_CLASSIFICATION D
   USING (SELECT CATEGORY_ID_NEW, CATEGORY_ID_OLD, ACCOUNT_ID FROM DATA_TEMP) S
   ON (D.SELLER_ACCOUNT_ID = S.ACCOUNT_ID AND D.CATEGORY_ID = S.CATEGORY_ID_OLD)
   WHEN MATCHED THEN UPDATE SET D.CATEGORY_ID = S.CATEGORY_ID_NEW;

Solution

  • If there is 1:1 relationship use a simple update:

    update t_evm_classification d set category_id = (
      select category_id_new 
        from data_temp s
        where d.seller_account_id = s.account_id 
          and d.category_id = s.category_id_old )
    

    The above update will nullify category_id if data in temp table is not present. To avoid this add where clause:

    update t_evm_classification d 
      set category_id = ( select category_id_new 
                            from data_temp s
                           where d.seller_account_id = s.account_id 
                             and d.category_id = s.category_id_old )
      where (d.seller_account_id, d.category_id) in 
        (select s.account_id, category_id_old from data_temp s)