4

I'm trying to implement a solution I found over here from Michiel de Mare to update multiple records with one (preferably-simple-in-a-syntax-sense) query. The example code that I am trying to learn from looks like this:

INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12) ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);

I'm using Oracle (and am not yet well versed in SQL queries).

Based on some dynamic content, I have concatenated my query similar to the above. It can vary in length depending on how many records I am updating, but this is an example of a query that I generated:

INSERT INTO my_table (question_id,ug) VALUES (30,0),(31,1) ON DUPLICATE KEY UPDATE ug=VALUES(ug)

The above query is getting this error:

Native message: ORA-00933: SQL command not properly ended

I am dealing with a content management system that has a function call that runs the queries; within this framework. I don't think it is pertinent, but I have never needed to put a ';' on the end of queries, however, I tried it with and without the semicolon.

Community
  • 1
  • 1
John
  • 41
  • 1
  • 1
  • 3

1 Answers1

13

Oracle doesn't have on duplicate key update Use MERGE instead:

MERGE INTO my_table trg  
USING (SELECT 30 as question_id,0 as ug FROM DUAL
UNION ALL 
SELECT 31,1 FROM DUAL) src ON (src.question_id = trg.question_id)
WHEN NOT MATCHED THEN INSERT(question_id, ug) VALUES
(src.question_id, src.ug)
WHEN MATCHED THEN UPDATE
SET trg.ug = src.ug
a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • 6
    @mauek unak: Oracle database doesn't have `ON DUPLICATE KEY UPDATE`. The question is about Oracle database, not about Mysql which is owned by Oracle corp. Documentation you provided is about Mysql 5.1 . :) – a1ex07 Apr 12 '16 at 15:32