Oracle 插入如果不存在语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10824764/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle insert if not exists statement
提问by user1427096
insert into OPT (email, campaign_id) values('[email protected]',100)
where not exists( select * from OPT where (email ="[email protected]" and campaign_id =100)) ;
Error report: SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause:
*Action:
错误报告：SQL 错误：ORA-00933：SQL 命令未正确结束 00933. 00000 - “SQL 命令未正确结束” *原因：
*操作：
how to insert a new row if it doesn't exists in Oracle?
如果 Oracle 中不存在新行，如何插入新行？
回答by a_horse_with_no_name
insert into OPT (email, campaign_id)
select '[email protected]',100
from dual
where not exists(select *
from OPT
where (email ="[email protected]" and campaign_id =100));
回答by p.marino
回答by René Nyffenegger
insert into OPT (email, campaign_id)
select 'mom@coxnet' as email, 100 as campaign_id from dual MINUS
select email, campaign_id from OPT;
If there is already a record with [email protected]/100in OPT, the MINUSwill subtract this record from the select 'mom@coxnet' as email, 100 as campaign_id from dualrecord and nothing will be inserted. On the other hand, if there is no such record, the MINUSdoes not subract anything and the values mom@coxnet/100will be inserted.
如果OPT 中已经有一条带[email protected]/的记录100，MINUS则将从记录中减去该记录，select 'mom@coxnet' as email, 100 as campaign_id from dual并且不会插入任何内容。另一方面，如果没有这样的记录，MINUS则不减去任何内容，值mom@coxnet/100将被插入。
As p.marinohas already pointed out, mergeis probably the better (and more correct) solution for your problem as it is specifically designed to solve your task.
正如p.marino已经指出的那样，merge对于您的问题可能是更好（更正确）的解决方案，因为它是专门为解决您的任务而设计的。
回答by ayush baran
MERGE INTO OPT
USING
(SELECT 1 "one" FROM dual)
ON
(OPT.email= '[email protected]' and OPT.campaign_id= 100)
WHEN NOT matched THEN
INSERT (email, campaign_id)
VALUES ('[email protected]',100)
;
