SQL command not properly ended, on an insert/on duplicate

Question

I've isolated my problem down to this code, which is giving me the error message "ORA-00933: SQL command not properly ended" for the on duplicate key line.

I'm trying to insert rows unless they have a duplicate key, in which case I want to update them instead. Something like insert...select and on duplicate key update.

I can see it must be a syntax problem, but I don't normally work with SQL so any help is appreciated.

insert into "tableB" ("col1", "col2")
select      "tableA"."colX", "tableA"."colY"
from        "tableA"
on duplicate key update "tableB"."col1" = "tableA"."colX";

score 2 · Answer 1 · answered Jun 23 '13 at 04:10

2

I would look into using MERGE:

MERGE INTO tableB b
USING tableA a
    ON (b.col1 = a.colX)
WHEN     MATCHED THEN UPDATE SET b.col2 = a.colY
WHEN NOT MATCHED THEN INSERT( col1, col2 )
VALUES(a.colX, a.colY);

SQL Fiddle Demo

Note: this is presuming your key between the two tables is col1 and colx.

answered Jun 23 '13 at 04:10

sgeddes

62,311
6
61
83

How is MERGE different from INSERT...SELECT? i.e. is there anything I should be aware of before making the change? – stringy Jun 23 '13 at 07:54
Edited to add: because I made the change and it's working :) – stringy Jun 23 '13 at 08:13
@stringy - it's mostly different because Oracle doesn't have the `on duplicate key` syntax. – Alex Poole Jun 23 '13 at 08:19

SQL command not properly ended, on an insert/on duplicate

1 Answers1