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