1

I found a very similar topic on Oracle Equivalent to MySQL INSERT IGNORE? However, I could not make work any of the proposed solutions. My case is a little special as my table does contains only 1 field, which is the primary key. Let's call the field "id" and the table "myTable" in the following.

Using MERGE

merge into myTable t1 from (
    select 42 as the_pk_value, 'TEST' as some_column from dual
) t2 on (t1.id = t2.the_pk_value)
when not matched then 
    insert (id) values (t2.the_pk_value);

This first attempt gives a SQL Error: ORA-02012: missing USING keyword error message. I have to admit that I did NOT understand the proposed syntax, so maybe I messed something when adapting to my pk-only-table. But as the showed example did not use any USING keyword, I dont understand where my error can be.

Using hint

insert /*+ ignore_row_on_dupkey_index(SPSECU, PK_SPSECU) */ into myTable (id) values ('TEST');

This does work through SQL Developer, however it does not work from Java (I suspect OJDBC Driver to remove the comments to reduce transfer size. BTW I could not figure out which JDBC driver I'm using... the Spring Tool Source bundle seems to connect without any further configuration to the Oracle Database. I see only a DERBY default driver installed.

Using NOT EXISTS

I could not make this syntax work. Here is what I wrote :

insert into myTable (id) values ('TEST') where not exists (select id from myTable where id='TEST');

I get an SQL Error: ORA-00933: SQL command not properly ended error with this version.

Using insert select

I did not understand anything of the proposed solution by knagaev... trying to adapt it to my table gave me this :

insert into myTable t1 select id from myTable t2 where not exists (select 1 from t1 where t1.id = 'TEST');

Can someone help me ? I'm used to MySQL INSERT IGNORE simple syntax and am quite new on Oracle (using 11g version).

kaya3
  • 47,440
  • 4
  • 68
  • 97
Wis
  • 705
  • 1
  • 11
  • 34

1 Answers1

1

Merge uses using instead of from:

merge into myTable t1 USING (
    select 42 as the_pk_value, 'TEST' as some_column from dual
) t2 on (t1.id = t2.the_pk_value)
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Your syntax indeed work using SQL Developer. But... for some reason, the `ORA-00001: unique constraint (PK_MYTABLE) violated` does appear when called from Java. The error message does print the exact SQL query and it works perfectly when I copy-paste it in SQL Developer (first message : `1 rows merged.` following messages : `0 rows merged.`). Any idea ? – Wis May 07 '13 at 10:16
  • Not sure, I'm not a Java expert. Perhaps post a new Java question with the code snippet? – Andomar May 07 '13 at 10:40
  • I suspect something with the Oracle driver... For now I'll accept your solution, you gave me a valid Oracle answer (even if it fails through Java call). – Wis May 07 '13 at 11:51