如果不存在，则插入oracle [英] insert if not exists oracle

问题描述

我需要能够运行一个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;

使用Oracle完全可行吗?

Is this at all possible with Oracle?

_{如果您能告诉我如何在PostgreSQL或MySQL中执行此操作，则奖励积分.}

推荐答案

该语句称为MERGE.查一下，我太懒了.

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

但是请注意，MERGE不是原子的，这可能导致以下效果(感谢Marius):

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

这篇关于如果不存在，则插入oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！