CODEWITHSUNDEEP
sqloracle-databasesql-merge
Nital
Nital

Reputation: 6114

Not able to merge records in Oracle database using merge statement

SELECT * FROM DIM_TRANS_TYPE WHERE TRANSACTION_TYPE='ILAU';

enter image description here

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:

Upvotes: 0

Views: 234

Answers (2)

user5683823
user5683823

Reputation:

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?

Upvotes: 0

trincot
trincot

Reputation: 351369

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.

Upvotes: 1

Related Questions