Insert if not exists Oracle

Question

I need to be able to run an Oracle query which goes to insert a number of rows, but it also checks to see if a primary key exists and if it does, then it skips that insert. Something like:

INSERT ALL     IF NOT EXISTS( SELECT 1 WHERE fo.primary_key='bar' )     (         INSERT INTO              schema.myFoo fo ( primary_key, value1, value2 )         VALUES             ('bar','baz','bat')     ),          IF NOT EXISTS( SELECT 1 WHERE fo.primary_key='bar1' )     (         INSERT INTO              schema.myFoo fo ( primary_key, value1, value2 )         VALUES             ('bar1','baz1','bat1')     ) SELECT * FROM schema.myFoo; 

Is this at all possible with Oracle?

_{Bonus points if you can tell me how to do this in PostgreSQL or MySQL.}

Answer 1

Coming late to the party, but...

With oracle 11.2.0.1 there is a semantic hint that can do this: IGNORE_ROW_ON_DUPKEY_INDEX

Example:

insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(customer_orders,pk_customer_orders) */   into customer_orders        (order_id, customer, product) values (    1234,     9876,  'K598')      ; 

---

UPDATE: Although this hint works (if you spell it correctly), there are better approaches which don't require Oracle 11R2:

First approach—direct translation of above semantic hint:

begin   insert into customer_orders          (order_id, customer, product)   values (    1234,     9876,  'K698')   ;   commit; exception   when DUP_VAL_ON_INDEX   then ROLLBACK; end; 

Second aproach—a lot faster than both above hints when there's a lot of contention:

begin     select count (*)     into   l_is_matching_row     from   customer_orders     where  order_id = 1234     ;      if (l_is_matching_row = 0)     then       insert into customer_orders              (order_id, customer, product)       values (    1234,     9876,  'K698')       ;       commit;     end if; exception   when DUP_VAL_ON_INDEX   then ROLLBACK; end; 

Answer 2

The statement is called MERGE. Look it up, I'm too lazy.

Beware, though, that MERGE is not atomic, which could cause the following effect (thanks, Marius):

SESS1:

create table t1 (pk int primary key, i int); create table t11 (pk int primary key, i int); insert into t1 values(1, 1); insert into t11 values(2, 21); insert into t11 values(3, 31); commit; 

SESS2: insert into t1 values(2, 2);

SESS1:

MERGE INTO t1 d USING t11 s ON (d.pk = s.pk) WHEN NOT MATCHED THEN INSERT (d.pk, d.i) VALUES (s.pk, s.i); 

SESS2: commit;

SESS1: ORA-00001