Oracle插入不存在语句

Question

insert into OPT (email, campaign_id) values('[e-mail]',100)
where not exists( select * from OPT where (email ="[e-mail]" and campaign_id =100)) ;

错误报告：SQL错误：ORA-00933：SQL命令未正确结束 00933. 00000 - “SQL命令未正确结束” *原因：
*操作：

如何在Oracle中插入一行数据，如果该行不存在？

- user1427096

Answer 1

insert into OPT (email, campaign_id) 
select '[e-mail]',100
from dual
where not exists(select * 
                 from OPT 
                 where (email ='[e-mail]' and campaign_id =100));

- user330315

---

简单而灵活的批量插入。谢谢。 - Farid

Answer 2

在Oracle中根据已存在的另一条记录插入数据的正确方法是使用MERGE语句。

请注意，这个问题在SO上已经有答案了:

• oracle insert if row not exists
• insert if not exists oracle

- p.marino

Answer 3

MERGE INTO OPT
USING
    (SELECT 1 "one" FROM dual) 
ON
    (OPT.email= '[e-mail]' and OPT.campaign_id= 100) 
WHEN NOT matched THEN
INSERT (email, campaign_id)
VALUES ('[e-mail]',100) 
;

- ayush baran

---

评论中说：你好，请不要只回答源代码，尝试提供有关您解决方案的良好描述。请参阅：如何编写一个好答案？谢谢。 - sɐunıɔןɐqɐp

Answer 4

insert into OPT       (email,        campaign_id) 
select 'mom@coxnet' as email, 100 as campaign_id from dual MINUS
select                 email,        campaign_id from OPT;

如果在OPT中已经存在具有[e-mail]/100的记录，则MINUS将从select 'mom@coxnet' as email, 100 as campaign_id from dual记录中减去此记录，并且不会插入任何内容。另一方面，如果没有这样的记录，MINUS就不会减去任何东西，值mom@coxnet/100将被插入。

正如p.marino所指出的那样，merge可能是您问题的更好（也更正确）的解决方案，因为它专门设计用于解决您的任务。

- René Nyffenegger

Answer 5

另一种方法是利用Oracle中的INSERT ALL语法。

INSERT ALL 
    INTO table1(email, campaign_id) VALUES (email, campaign_id)
WITH source_data AS
 (SELECT '[e-mail]' email,100 campaign_id
  FROM   dual
  UNION ALL
  SELECT '[e-mail]' email,200 campaign_id
  FROM   dual)      
SELECT email
      ,campaign_id
FROM   source_data src
WHERE  NOT EXISTS (SELECT 1
        FROM   table1 dest
        WHERE  src.email = dest.email
        AND    src.campaign_id = dest.campaign_id);

INSERT ALL也允许我们根据子查询作为源，在多个表中执行有条件的插入操作。

有一些非常简洁和好的例子可供参考。

1. oracletutorial.com
2. oracle-base.com/

- Sujitmohanty30