Not able to merge records in Oracle database using merge statement

Question

SELECT * FROM DIM_TRANS_TYPE WHERE TRANSACTION_TYPE='ILAU';

All I want to accomplish here is change ILAU to IFAU and Instant Loan Authorization Request to Instant Finance Authorization Request in the above-mentioned record.

Table Schema:

Name                  Null     Type               
--------------------- -------- ------------------ 
TRANSACTION_TYPE      NOT NULL VARCHAR2(4 CHAR)   --> PRIMARY KEY
TRANSACTION_TYPE_DESC          VARCHAR2(256 CHAR)

WORKS !!!

MERGE INTO DIM_TRANS_TYPE a
USING (SELECT 'ILAU' TRANSACTION_TYPE, 'Instant Loan Authorization Request' TRANSACTION_TYPE_DESC FROM DUAL) b
    ON (a.TRANSACTION_TYPE = b.TRANSACTION_TYPE)
WHEN MATCHED THEN UPDATE
    SET
      a.TRANSACTION_TYPE_DESC = 'Instant Finance Authorization Request'
WHEN NOT MATCHED THEN
    INSERT(a.TRANSACTION_TYPE, a.TRANSACTION_TYPE_DESC)
    VALUES(b.TRANSACTION_TYPE, b.TRANSACTION_TYPE_DESC);

DOES NOT WORK !!! (Error shown below)

MERGE INTO DIM_TRANS_TYPE a
USING (SELECT 'ILAU' TRANSACTION_TYPE, 'Instant Loan Authorization Request' TRANSACTION_TYPE_DESC FROM DUAL) b
    ON (a.TRANSACTION_TYPE = b.TRANSACTION_TYPE)
WHEN MATCHED THEN UPDATE
    SET
      a.TRANSACTION_TYPE = 'IFAU',
      a.TRANSACTION_TYPE_DESC = 'Instant Finance Authorization Request'
WHEN NOT MATCHED THEN
    INSERT(a.TRANSACTION_TYPE, a.TRANSACTION_TYPE_DESC)
    VALUES(b.TRANSACTION_TYPE, b.TRANSACTION_TYPE_DESC);

Error:

Error at Command Line : 5 Column : 9
Error report -
SQL Error: ORA-38104: Columns referenced in the ON Clause cannot be updated: "A"."TRANSACTION_TYPE"
38104. 00000 -  "Columns referenced in the ON Clause cannot be updated: %s"
*Cause:    LHS of UPDATE SET contains the columns referenced in the ON Clause
*Action:

the error message is clear and it is one of the restrictions of `merge` that you can't update the columns being referenced in `on`. check the documenation https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606 — Vamsi Prabhala, Jun 07 '17 at 20:51
The error seems pretty straightforward, you can't update the column matching in the ON clause (a.TRANSACTION_TYPE). Can you insert into a new table then update your original table so they aren't the same column? — Jacob H, Jun 07 '17 at 20:51

score 1 · Accepted Answer · answered Jun 07 '17 at 21:04

As the error message indicates, it is not possible to update the fields that are used in the on clause.

You'll have to do this kind of update in a separate statement. Something like this:

UPDATE DIM_TRANS_TYPE
SET    TRANSACTION_TYPE = 'IFAU',
       TRANSACTION_TYPE_DESC = 'Instant Finance Authorization Request'
WHERE  TRANSACTION_TYPE IN (
            SELECT TRANSATION_TYPE
            FROM   (SELECT 'ILAU' TRANSACTION_TYPE, 
                           'Instant Loan Authorization Request' TRANSACTION_TYPE_DESC 
                    FROM DUAL)
       );

MERGE INTO DIM_TRANS_TYPE a
USING      (SELECT 'ILAU' TRANSACTION_TYPE, 
                   'Instant Loan Authorization Request' TRANSACTION_TYPE_DESC 
            FROM   DUAL) b
        ON (a.TRANSACTION_TYPE = b.TRANSACTION_TYPE)
WHEN NOT MATCHED THEN
    INSERT(TRANSACTION_TYPE, TRANSACTION_TYPE_DESC)
    VALUES(b.TRANSACTION_TYPE, b.TRANSACTION_TYPE_DESC);

You may want to execute this in a transaction, so that the changes incurred by either both or neither of these statements are committed.

Of course, the IN clause looks a bit exaggerated as the value could simply be compared with ILAU, but I assume you simplified the question when in reality you have a bigger dataset to get the transaction_type values from.

score 0 · Answer 2 · answered Jun 07 '17 at 21:33

Perhaps I am missing something.... It seems you are looking to identify all the rows where transaction_type = 'ILAU' (regardless of transaction_type_desc), and for those rows to update transaction_type to 'IFAU' and transaction_type_desc to 'Instant Finance Authorization Request'. Right?

If so, why do you need anything other than a trivial UPDATE statement?

update  dim_trans_type
  set   transaction_type      = 'IFAU',
        transaction_type_desc = 'Instant Finance Authorization Request'
  where transaction_type      = 'ILAU'
;

In your attempts there is also a when not matched clause - inserting a row if none of the existing rows have transaction_type = 'ILAU'. How is that related to your requirement? You said All I want to accomplish here is change [.....] - no mention of inserting anything. Is the problem statement, in plain English, incomplete? Or are you inserting things that don't need to be inserted?

Not able to merge records in Oracle database using merge statement

2 Answers2