使用 SQL 进行条件插入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16636698/
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
Do conditional INSERT with SQL?
提问by Elmi
I have a database that is updated with datasets from time to time. Here it may happen that a dataset is delivered that already exists in database.
我有一个不时更新数据集的数据库。这里可能会发生传递的数据集已经存在于数据库中。
Currently I'm first doing a
目前我首先做一个
SELECT FROM ... WHERE val1=... AND val2=...
to check, if a dataset with these data already exists (using the data in WHERE-statement). If this does not return any value, I'm doing my INSERT.
检查具有这些数据的数据集是否已经存在（使用 WHERE 语句中的数据）。如果这没有返回任何值，我正在做我的插入。
But this seems to be a bit complicated for me. So my question: is there some kind of conditional INSERT that adds a new dataset only in case it does not exist?
但这对我来说似乎有点复杂。所以我的问题是：是否有某种条件 INSERT 仅在新数据集不存在的情况下添加它？
I'm using SmallSQL
我正在使用SmallSQL
回答by DougM
You can do that with a single statement and a subquery in nearly all relational databases.
您可以在几乎所有关系数据库中使用单个语句和子查询来完成此操作。
INSERT INTO targetTable(field1)
SELECT field1
FROM myTable
WHERE NOT(field1 IN (SELECT field1 FROM targetTable))
Certain relational databases have improved syntax for the above, since what you describe is a fairly common task. SQL Server has a MERGEsyntax with all kinds of options, and MySQL has optional INSERT OR IGNOREsyntax.
某些关系数据库对上述语法进行了改进，因为您描述的是一项相当常见的任务。SQL Server 有MERGE各种选项的语法，而 MySQL 有可选INSERT OR IGNORE语法。
Edit:SmallSQL's documentationis fairly sparse as to which parts of the SQL standard it implements. It may not implement subqueries, and as such you may be unable to follow the advice above, or anywhere else, if you need to stick with SmallSQL.
编辑：SmallSQL 的文档很少涉及它实现的 SQL 标准的哪些部分。它可能不实现子查询，因此如果您需要坚持使用 SmallSQL，您可能无法遵循上述建议或其他任何地方。
回答by Fabian Bigler
I dont know about SmallSQL, but this works for MSSQL:
我不知道 SmallSQL，但这适用于 MSSQL：
IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)
Based on the where-condition, this updates the row if it exists, else it will insert a new one.
根据 where 条件，如果该行存在，则更新该行，否则将插入一个新行。
I hope that's what you were looking for.
我希望这就是你要找的。
回答by TMtech
It is possible with EXISTScondition. WHERE EXISTStests for the existence of any records in a subquery. EXISTSreturns true if the subquery returns one or more records.
Here is an example
EXISTS有条件是可以的。WHERE EXISTS测试子查询中是否存在任何记录。EXISTS如果子查询返回一条或多条记录，则返回 true。这是一个例子
UPDATE TABLE_NAME
SET val1=arg1 , val2=arg2
WHERE NOT EXISTS
(SELECT FROM TABLE_NAME WHERE val1=arg1 AND val2=arg2)
回答by ZhiHeather
Usually you make the thing you don't want duplicates of unique, and allow the database itself to refuse the insert.
通常你会制作你不想要的重复的唯一的东西，并允许数据库本身拒绝插入。
Otherwise, you can use INSERT INTO, see How to avoid duplicates in INSERT INTO SELECT query in SQL Server?
否则，您可以使用 INSERT INTO，请参阅如何在 SQL Server 中的 INSERT INTO SELECT 查询中避免重复？
回答by Matthew
If you're looking to do an "upsert" one of the most efficient ways currently in SQL Server for single rows is this:
如果您想对 SQL Server 中的单行进行“更新插入”，目前最有效的方法之一是：
UPDATE myTable ...
IF @@ROWCOUNT=0
INSERT INTO myTable ....
You can also use the MERGEsyntax if you're doing this with sets of data rather than single rows.
MERGE如果您对数据集而不是单行执行此操作，也可以使用该语法。
If you want to INSERTand not UPDATEthen you can just write your single INSERTstatement and use WHERE NOT EXISTS (SELECT ...)
如果你想INSERT而不是UPDATE那么你可以只写你的单一INSERT语句并使用WHERE NOT EXISTS (SELECT ...)
