C# SQL - 仅当记录不存在时才插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18689420/
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
SQL - INSERT INTO only when the record does not exist
提问by Brett
I realize there a lot of answered SO question on this same topic, but I can't seem to use any of that information to figure out what's wrong with my query.
我意识到在同一主题上有很多已回答的 SO 问题，但我似乎无法使用任何这些信息来找出我的查询出了什么问题。
I'm trying to insert a record into my table only if a record with the same database_ownerand database_name(those are two of my column names) does not already exist.
仅当具有相同database_owner和database_name（这是我的两个列名）的记录不存在时，我才尝试将记录插入到我的表中。
I'm using the query:
我正在使用查询：
INSERT INTO users_databases (database_name, database_key, database_secret, database_owner)
VALUES ('DB1', '263f690d-7ac3-49f2-aa3b-f5672e4639a2', '367123d8-e5a7-46a0-8101-21f39e6ac8d9', '[email protected]')
WHERE
NOT EXISTS
(SELECT database_name FROM uses_databases WHERE database_name = 'DB1' AND database_owner = '[email protected]');
But I'm getting the error Incorrect syntax near the keyword 'WHERE'.
但我收到错误 Incorrect syntax near the keyword 'WHERE'.
I should also point out that running the query
我还应该指出运行查询
SELECT database_name
FROM uses_databases
WHERE database_name = 'DB1' AND database_owner = '[email protected]'
does return a record.
确实返回记录。
Where could I be going wrong? From what I've read in the other questions, this looks correct. But it's obviously not. :-) Any advice would be super helpful.
我哪里会出错？从我在其他问题中读到的内容来看，这看起来是正确的。但显然不是。:-) 任何建议都会非常有帮助。
采纳答案by p.s.w.g
The easiest solution would be something like this:
最简单的解决方案是这样的：
IF NOT EXISTS (SELECT database_name FROM uses_databases WHERE database_name = 'DB1' AND database_owner = '[email protected]')
INSERT INTO users_databases (database_name, database_key, database_secret, database_owner)
VALUES ('DB1', '263f690d-7ac3-49f2-aa3b-f5672e4639a2', '367123d8-e5a7-46a0-8101-21f39e6ac8d9', '[email protected]');
回答by a1ex07
You can also do
你也可以这样做
INSERT INTO users_databases (database_name, database_key, database_secret, database_owner)
SELECT 'DB1', '263f690d-7ac3-49f2-aa3b-f5672e4639a2',
'367123d8-e5a7-46a0-8101-21f39e6ac8d9', '[email protected]'
WHERE
NOT EXISTS
(SELECT database_name
FROM uses_databases
WHERE database_name = 'DB1' AND database_owner = '[email protected]'
)
回答by Basem Sayej
You can do the following, must work
您可以执行以下操作，必须工作
INSERT INTO users_databases (database_name, database_key, database_secret, database_owner)
VALUES ('DB1', '263f690d-7ac3-49f2-aa3b-f5672e4639a2', '367123d8-e5a7-46a0-8101- 21f39e6ac8d9', '[email protected]')
WHERE 1
NOT IN
(SELECT 1 FROM uses_databases WHERE database_name = 'DB1' AND database_owner = '[email protected]');
