Upserting Oracle Table using Merge ORA-01747

Question

Upserting an oracle table table 1 having schema

table1 (col1,col2,col3,Primary key :Col2).

and the upsert statement

MERGE
 INTO table1
USING (
         SELECT ? AS Col1
              , ? AS Col2
              , ? AS Col3
           FROM dual
      ) maybe 
   ON ( maybe.Col2=table1.Col2 )
 WHEN NOT MATCHED THEN
          INSERT VALUES (
               maybe.Col1
             , maybe.Col2
             , maybe.Col3
          )
 WHEN MATCHED THEN
          UPDATE SET table1.Col1=maybe.Col1
               , SET table1.Col3=maybe.Col3
 ;

Result Exception occurred java.sql.BatchUpdateException: ORA-01747: invalid user.table.column, table.column, or column specification

Alex Poole Alex Poole · Accepted Answer · 2014-05-08T11:04:06

You have an extra SET in the UPDATE section:

MERGE INTO table1
USING ( SELECT  ? AS Col1 , ? AS Col2 , ? AS Col3  FROM dual ) maybe
ON ( maybe.Col2=table1.Col2 )
WHEN NOT MATCHED THEN INSERT VALUES ( maybe.Col1, maybe.Col2, maybe.Col3 )
WHEN MATCHED THEN UPDATE SET table1.Col1=maybe.Col1, SET table1.Col3=maybe.Col3
                                                     ^^^

It should be:

WHEN MATCHED THEN UPDATE SET table1.Col1=maybe.Col1, table1.Col3=maybe.Col3

Quick SQL Fiddle.

The table1 specifiers in that aren't needed, since the update is only applied against the target table anyway, but they don't hurt.

Upserting Oracle Table using Merge ORA-01747

1 Answers