getting error on Merge Statement - ORA-01747

Question

Am new to oracle database(been working on DB2 and cobol). i have a question on Merge Statement. From Javascript, am trying to insert a row into a table in Oracle DB. if the insert fails, then the plan is to update. I have used Merge Statements in DB2 and when checked, the Merge SQL in oracle is similar to what i have used in DB2. Below is my Merge Statement.

MERGE INTO TBL_1 O USING (SELECT COL1, COL2, COL3 FROM TBL_1 WHERE COL1='A' AND COL2='B' AND COL3 = 'C') M ON (O.COL1 = M.COL1 AND O.COL2 = M.COL2 AND O.COL3=M.COL3) WHEN MATCHED THEN UPDATE SET O.COL4 = 'XYZ' ,O.COL5 = 123 WHEN NOT MATCHED THEN INSERT ((O.COL1, O.COL2, O.COL3, O.COL4, O.COL5) VALUES ('A','B','C','XYZ',123)) ;

When i execute the query in sql developer tool, i get an error message SQL Error: ORA-01747: invalid.user.table.column, or column specification

Appreciate any help on this. Thanks

Answer 1

I've used merge statements before and the only thing I see suspect is an extra pair of parens on the INSERT clause. I would also drop the table alias from the column list.

A column name could also be wrong but I can't validate for you from what's been given.

 MERGE INTO TBL_1 O 
    USING 
    (SELECT COL1, 
        COL2, 
        COL3 FROM TBL_1 
      WHERE COL1='A' 
        AND COL2='B' 
        AND COL3 = 'C') M 
    ON (O.COL1 = M.COL1 AND O.COL2 = M.COL2 AND O.COL3=M.COL3) 

    WHEN MATCHED THEN UPDATE 
        SET O.COL4 = 'XYZ' 
           ,O.COL5 = 123 
    WHEN NOT MATCHED THEN INSERT
    (   COL1, 
        COL2, 
        COL3, 
        COL4, 
        COL5
    ) VALUES ('A','B','C','XYZ',123) ;