Oracle insert if row does not exist

Question

insert ignore into table1 
select 'value1',value2 
from table2 
where table2.type = 'ok'

When I run this I get the error "missing INTO keyword".

Answer 1

When I run this I get the error "missing INTO keyword" .

Because IGNORE is not a keyword in Oracle. That is MySQL syntax.

What you can do is use MERGE.

merge into table1 t1
    using (select 'value1' as value1 ,value2 
           from table2 
           where table2.type = 'ok' ) t2
    on ( t1.value1 = t2.value1)
when not matched then
   insert values (t2.value1, t2.value2)
/

From Oracle 10g we can use merge without handling both branches. In 9i we had to use a "dummy" MATCHED branch.

In more ancient versions the only options were either :

1. test for the row's existence before issuing an INSERT (or in a sub-query);
2. to use PL/SQL to execute the INSERT and handle any resultant DUP_VAL_ON_INDEX error.

Answer 2

Note that if you are lucky enough to work with version 11g Release 2, you can use the hint IGNORE_ROW_ON_DUPKEY_INDEX.

INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(table1(id)) */ INTO table1 SELECT ...

From the documentation: http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/sql_elements006.htm#CHDEGDDG

An example from my blog: http://rwijk.blogspot.com/2009/10/three-new-hints.html

Regards, Rob.

Answer 3

Because you typed the spurious word "ignore" between "insert" and "into"!!

insert ignore into table1 select 'value1',value2 from table2 where table2.type = 'ok'

Should be:

insert into table1 select 'value1',value2 from table2 where table2.type = 'ok'

From your question title "oracle insert if row not exists" I assume you thought "ignore" was an Oracle keyword that means "don't try to insert a row if it already exists". Maybe this works in some other DBMS, but it doesn't in Oracle. You could use a MERGE statement, or check for existence like this:

insert into table1 
select 'value1',value2 from table2 
where table2.type = 'ok'
and not exists (select null from table1
                where col1 = 'value1'
                and col2 = table2.value2
               );