SQL query with ON DUPLICATE KEY UPDATE clarification needed [duplicate]

Question

I'm trying to use the following query:

   INSERT INTO table1(col1, col2, col3)
   SELECT ss.col1, ss.col2, ss.col3
   FROM table2 ss
   ON DUPLICATE KEY UPDATE
   col1=ss.col1,
   col2=ss.col2,
   col3=ss.col3;

Unfortunately all I get is "SQL query not properly ended" message. Where in this example the syntax is wrong?

The error message pops up right when I insert

ON DUPLICATE KEY UPDATE
       col1=ss.col1,
       col2=ss.col2,
       col3=ss.col3;

I found this query on stackoverflow, it should be correct, but it does not work for me. Maybe I missed something? — minerals
– minerals, Commented Mar 1, 2013 at 15:50
"on duplicate key update" only works for MySql, oracle DB requires the use of the merge keyword. — cornergraf
– cornergraf, Commented Jan 8, 2016 at 12:44

Justin Cave · Accepted Answer · 2013-03-01 15:53:15Z

4

Oracle doesn't support the ON DUPLICATE KEY UPDATE syntax. That appears to be MySQL-specific syntax.

Most likely, you would appear to want a MERGE statement

MERGE INTO table1 t1
  USING (SELECT col1, col2, col3 
           FROM table2) ss
     ON (t1.col1 = ss.col1) -- whatever the key is
   WHEN MATCHED THEN
     UPDATE SET t1.col1 = ss.col1,
                t1.col2 = ss.col2,
                t1.col3 = ss.col3
   WHEN NOT MATCHED THEN 
     INSERT( t1.col1, t1.col2, t1.col3 )
       VALUES( ss.col1, ss.col2, ss.col3 )

answered Mar 1, 2013 at 15:53

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query with ON DUPLICATE KEY UPDATE clarification needed [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related