oracle 插入时if,insert if not exists oracle

问题

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.

回答1:

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

回答2:

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;

回答3:

This only inserts if the item to be inserted is not already present.

Works the same as:

if not exists (...) insert ...

in T-SQL

insert into destination (DESTINATIONABBREV)

select 'xyz' from dual

left outer join destination d on d.destinationabbrev = 'xyz'

where d.destinationid is null;

may not be pretty, but it's handy :)

回答4:

We can combine the DUAL and NOT EXISTS to archive your requirement:

INSERT INTO schema.myFoo (

primary_key, value1, value2

)

SELECT

'bar', 'baz', 'bat'

FROM DUAL

WHERE NOT EXISTS (

SELECT 1

FROM schema.myFoo

WHERE primary_key = 'bar'

);

回答5:

If you do NOT want to merge in from an other table, but rather insert new data... I came up with this. Is there perhaps a better way to do this?

MERGE INTO TABLE1 a

USING DUAL

ON (a.C1_pk= 6)

WHEN NOT MATCHED THEN

INSERT(C1_pk, C2,C3,C4)

VALUES (6, 1,0,1);

回答6:

It that code is on the client then you have many trips to the server so to eliminate that.

Insert all the data into a temportary table say T with the same structure as myFoo

Then

insert myFoo

select *

from t

where t.primary_key not in ( select primary_key from myFoo)

This should work on other databases as well - I have done this on Sybase

It is not the best if very few of the new data is to be inserted as you have copied all the data over the wire.

回答7:

DECLARE

tmp NUMBER(3,1);

BEGIN

SELECT COUNT(content_id) INTO tmp FROM contents WHERE (condition);

if tmp != 0 then

INSERT INTO contents VALUES (...);

else

INSERT INTO contents VALUES (...);

end if;

END;

I used the code above. It is long, but, simple and worked for me. Similar, to Micheal's code.

回答8:

This is an answer to the comment posted by erikkallen:

You don't need a temp table. If you

only have a few rows, (SELECT 1 FROM

dual UNION SELECT 2 FROM dual) will

do. Why would your example give

ORA-0001? Wouldn't merge take the

update lock on the index key and not

continue until Sess1 has either

committed or rolled back? – erikkallen

Well, try it yourself and tell me whether you get the same error or not:

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

回答9:

If your table is "independent" from others (I mean, it will not trigger a cascade delete or will not set any foreign keys relations to null), a nice trick could be to first DELETE the row and then INSERT it again. It could go like this:

DELETE FROM MyTable WHERE prop1 = 'aaa'; //assuming it will select at most one row!

INSERT INTO MyTable (prop1, ...) VALUES ('aaa', ...);

If your are deleting something which does not exist, nothing will happen.

回答10:

INSERT INTO schema.myFoo ( primary_key , value1 , value2 )

SELECT 'bar1' AS primary_key ,'baz1' AS value1 ,'bat1' AS value2 FROM DUAL WHERE (SELECT 1 AS value FROM schema.myFoo WHERE LOWER(primary_key) ='bar1' AND ROWNUM=1) is null;

来源：https://stackoverflow.com/questions/1702832/insert-if-not-exists-oracle