I found a very similar topic on <a href="https://stackoverflow.com/questions/9332360/oracle-equivalent-to-mysql-insert-ignore" rel="nofollow">Oracle Equivalent to MySQL INSERT IGNORE?</a> 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.
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.
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.
<strong>Using NOT EXISTS</strong>
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.
<strong>Using insert select</strong>
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).Answer1:
using instead of
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)