Oracle 如果不存在则插入

发布于 2024-08-10 01:39:37 字数 615 浏览 8 评论 0原文

我需要能够运行一个 Oracle 查询来插入多行，但它还会检查主键是否存在，如果存在，则跳过该插入。比如：

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 可以做到这一点吗？

_{如果你能告诉我如何在 PostgreSQL 或 MySQL 中执行此操作，我会加分。}

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

溺深海 2024-08-17 01:39:37

来晚了，但是...

在 oracle 11.2.0.1 中，有一个语义提示可以做到这一点：IGNORE_ROW_ON_DUPKEY_INDEX

示例：

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

UPDATE ：虽然这个提示有效（如果你拼写正确的话），但是还有更好的不需要 Oracle 11R2 的方法：

第一种方法 - 直接翻译上述语义提示：

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

第二种方法 - 当存在大量争用时，比上述两种提示快很多：

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;

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;

回复收藏 0 原文

猫卆 2024-08-17 01:39:37

该语句称为 MERGE。看看吧，我太懒了。

但请注意，MERGE 不是原子的，这可能会导致以下效果（谢谢 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: 提交；

SESS1：ORA-00001

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

回复收藏 0 原文

生寂 2024-08-17 01:39:37

我们可以结合DUAL和NOT EXISTS来实现您的要求：

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'
);

We can combine the DUAL and NOT EXISTS to achieve 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'
);

回复收藏 0 原文

说不完的你爱 2024-08-17 01:39:37

仅当要插入的项目尚不存在时才会插入。

工作原理与：

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

在 T-SQL 中

insert into destination (DESTINATIONABBREV) 
  select 'xyz' from dual 
  left outer join destination d on d.destinationabbrev = 'xyz' 
  where d.destinationid is null;

可能不太漂亮，但很方便:)

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 :)

回复收藏 0 原文

话少心凉 2024-08-17 01:39:37

如果您不想从其他表合并，而是插入新数据......我想出了这个。也许有更好的方法来做到这一点吗？

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);

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);

回复收藏 0 原文

生活了然无味 2024-08-17 01:39:37

如果代码位于客户端，那么您需要多次访问服务器，以便消除这种情况。

将所有数据插入到一个临时表中，比如 T ，其结构与 myFoo 相同

然后

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

这也应该适用于其他数据库 - 我已经在 Sybase 上完成了这一点

如果要像您一样插入很少的新数据，那不是最好的已通过网络复制了所有数据。

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.

回复收藏 0 原文

万人眼中万个我 2024-08-17 01:39:37

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;

我使用了上面的代码。它很长，但是很简单并且对我有用。与迈克尔的代码类似。

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.

回复收藏 0 原文

む无字情书 2024-08-17 01:39:37

如果您的表与其他表“独立”（我的意思是，它不会触发级联删除或不会将任何外键关系设置为空），一个不错的技巧可能是首先删除该行，然后再次插入它。它可以像这样：

DELETE FROM MyTable WHERE prop1 = 'aaa'; //假设它最多选择一行！

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

如果您要删除不存在的内容，则不会发生任何事情。

回复收藏 0 原文

寂寞美少年 2024-08-17 01:39:37

这是对 erikkallen 发表的评论的回答：

您不需要临时表。如果你
只有几行，（SELECT 1 FROM
双 UNION SELECT 2 FROM Dual) 将
做。为什么你的例子会给出
ORA-0001？不会合并
更新索引键上的锁而不是
继续，直到 Sess1 有
提交还是回滚？ – 埃里卡伦

好吧，你自己尝试一下，然后告诉我你是否遇到同样的错误：

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 value(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

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

回复收藏 0 原文

一身骄傲 2024-08-17 01:39:37

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;

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;

回复收藏 0 原文

~没有更多了~