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

Question

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;

what is the error you are getting – Rams Sep 01 '17 at 13:02 — Rams, Sep 01 '17 at 13:02
@Rams: The error is right in the title of this post. – Sep 01 '17 at 14:40 — , Sep 01 '17 at 14:40

score 2 · Answer 1 · answered Sep 01 '17 at 14:44

2

Move the "offending" condition from the ON clause to the WHERE clause:

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)
   WHEN MATCHED THEN UPDATE SET   D.CATEGORY_ID = S.CATEGORY_ID_NEW
                            WHERE D.CATEGORY_ID = S.CATEGORY_ID_OLD;

answered Sep 01 '17 at 14:44

Regarding that `WHERE` clause, it's worth mentioning that it doesn't seem to profit from any indexes on `BUNDLE_NAME`, so the `UPDATE` statement (or [some other workarounds](https://stackoverflow.com/a/54009449/521799)) definitely seems preferrable – Lukas Eder Jan 02 '19 at 16:02
@LukasEder - what is `BUNDLE_NAME`? I don't see it anywhere in the `MERGE` statement, so I don't really know what you are talking about. If you do, please explain. In any case: note that there are instances where `MERGE` will work while `UPDATE` will not - for example, if there are no duplicates in column `S.ACCOUNT_ID` but the column is not explicitly constrained to be `UNIQUE`. – Jan 02 '19 at 23:18
Sorry, wrong column name. I had just answered another question before, with `BUNDLE_NAME` as a column. In this case, it would be `CATEGORY_ID`. – Lukas Eder Jan 03 '19 at 09:12

Ponder Stibbons · Accepted Answer · 2017-09-01T13:21:45.393

1

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)

edited Sep 01 '17 at 13:21

answered Sep 01 '17 at 13:04

Ponder Stibbons

14,723
2
21
24

Thank you very much for the solution! This looks perfect, but throws ORA-01407: cannot update (%s) to NULL. I checked, and there are no NULLs in `data_temp` table. I also tried the inner select without the update: `select category_id_new from data_temp s, t_evm_classification d where d.seller_account_id = s.account_id and d.category_id = s.category_id_old` , and it returns correct data. – Konstantin Fedosov Sep 01 '17 at 13:34
@KonstantinFedosov - If you have data in the target table that doesn't have a match in the source table, the subquery will return no rows. If the subquery is used as a scalar subquery (as it is here, for assignment in an UPDATE statement), then "no rows" is treated as NULL, so an attempt is made to update `category_id` to `NULL`, for at least one row of the target table. The solution, here as in the original `MERGE` statement (I show in another Answer how to fix that), is to move the check on `category_id_old` to a `WHERE` clause. – Sep 01 '17 at 14:47
@KonstantinFedosov - Seems that `category_id` cannot be nullable. Good. Second version of my `update`, containing `where` protects us against update in such situation. The only other reason is that `data_temp.category_id_new` contains some null values. Anyway @mathguy's solution should be faster, so use it. – Ponder Stibbons Sep 01 '17 at 15:00

score 1 · Answer 3 · answered Jan 02 '19 at 16:02

There are a few possible workarounds which can be used to outsmart the parser, at least until Oracle 18c. One of them is to wrap your predicate in a row value expression predicate using an additional dummy column:

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, 'dummy') = ((S.CATEGORY_ID_OLD, 'dummy')))
   WHEN MATCHED THEN UPDATE SET D.CATEGORY_ID = S.CATEGORY_ID_NEW;

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

3 Answers3