Oracle equivalent of INSERT IGNORE

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);

​x
 
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');

 
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');

 
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');

 
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).

Answer

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)

 
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)​

Advertisement

Answer