带条件的 SQL 插入查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32039059/
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 Query With Condition
提问by Xavi
I am trying to insert values into 1 column of a table when a condition is satisfied.
我试图在满足条件时将值插入表的 1 列。
Note: The table already contains data for all the columns but for 1 which is empty. I would like to insert value into this 1 column depending on the WHERE clause.
注意：该表已包含所有列的数据，但 1 为空。我想根据 WHERE 子句在这 1 列中插入值。
I have this query:
我有这个查询：
INSERT INTO <TABLE_NAME>
(COLUMN_NAME)
(VALUE)
WHERE <CONDITION>
I am getting an exception:
我得到一个例外：
Incorrect Syntax Near WHERE Keyword
WHERE 关键字附近的语法不正确
I am able to do this using UPDATE:
我可以使用 UPDATE 来做到这一点：
UPDATE <TABLE_NAME>
SET <COL_NAME>
WHERE <CONDITION>
But was wondering why the INSERT query was failing. Any advise appreciated.
但想知道为什么 INSERT 查询失败。任何建议表示赞赏。
回答by HaveNoDisplayName
As I understand your problem, you already have data in one row, and one column in that row does not have value, so you want to add value in to that column.
据我了解您的问题，您已经在一行中有数据，而该行中的一列没有值，因此您希望向该列添加值。
This the scenario for Update existing row, not the insert new row. You have to use UPDATE clause when data already present and you want to modify record(s). Choose insert when You want to insert new row in table.
这是更新现有行的场景，而不是插入新行。当数据已经存在并且您想要修改记录时，您必须使用 UPDATE 子句。当您想在表中插入新行时选择插入。
So in your current scenario, Update Clause is your friend with Where Clause as you want to modify subset of records not all.
因此，在您当前的场景中，Update Clause 是 Where Clause 的朋友，因为您想修改记录的子集而不是全部。
UPDATE <TABLE_NAME>
SET <COL_NAME>
WHERE <CONDITION>
INSERT Clause does not have any Where Clause as per any RDBMS syntax(I think). Insert is condition less sql query, While SELECT, UPDATE, DELETE all are conditional commands, you can add Where Clause in all later ones.
根据任何 RDBMS 语法（我认为），INSERT 子句没有任何 Where 子句。插入是无条件的sql查询，而SELECT、UPDATE、DELETE都是有条件的命令，你可以在后面的所有语句中添加Where子句。
回答by TheBrokenSpoke
In order to add a value into the one column when the rows are already populated, you will need to use the update statement.
If you need to insert a new row that has a where clause, you will need to use an insert into select statement:
为了在行已填充时将值添加到一列中，您需要使用更新语句。
如果需要插入带有 where 子句的新行，则需要使用 insert into select 语句：
INSERT INTO <table> (<columns>)
SELECT <columns>
FROM <table>
WHERE <condition>;
回答by Rastislav
Based on your question I have the feeling that you are trying to UPDATE a column in a table rather than insert.
根据您的问题，我感觉您正在尝试更新表中的列而不是插入。
Something like:
就像是：
UPDATE column SET value WHERE different_column_value = some_value
回答by Prudhvi Konda
Insert statement will insert a new record. You cannot apply a where clause to the record that you are inserting.
Insert 语句将插入一条新记录。您不能将 where 子句应用于要插入的记录。
The where clause can be used to update the row that you want. update SET = where .
where 子句可用于更新所需的行。更新 SET = where 。
But insert will not have a where clause. Hope this answers your question
但是 insert 不会有 where 子句。希望这能回答你的问题
回答by aasanchez
The SQL Insert dont accept where parameters, you could check this: SQL Insert Definition...
SQL Insert 不接受 where 参数，您可以检查一下：SQL Insert Definition...
I do not know the whole question of what you want to do, but just using the INSERT statement is not possible, however it is possible to condition the insertion of data into a table, if this data is dependent on another table or comes from another table ... check here... SQL Insert explain in wikipedia
我不知道你想做什么的整个问题，但只使用 INSERT 语句是不可能的，但是如果此数据依赖于另一个表或来自另一个表，则可以将数据插入到表中表......在这里检查...... SQL Insert 解释在维基百科
like this:
像这样：
Copying rows from other tables
从其他表复制行
INSERT INTO phone_book2
SELECT *
FROM phone_book
WHERE name IN ('John Doe', 'Peter Doe')
or
或者
INSERT INTO phone_book2 ( [name], [phoneNumber] )
SELECT [name], [phoneNumber]
FROM phone_book
WHERE name IN ('John Doe', 'Peter Doe')
回答by o-o-awake-o-o
I take it the code you included is simply a template to show how you structured your query. See the SO questions here,hereand the MSDN question here.
我认为您包含的代码只是一个模板，用于显示您如何构建查询。请参阅此处的 SO 问题，此处以及此处的 MSDN 问题。
In SQL Server (which uses Transact-SQL aka T-SQL) you need an UPDATEquery for INSERTwhere columns already have values - by using the answer @HaveNoDisplayNamegave :)
在 SQL Server（使用 Transact-SQL 又名 T-SQL）中，您需要UPDATE查询INSERT列已经有值的位置 - 通过使用@HaveNoDisplayName给出的答案:)
If you are executing INSERT/ UPDATEfrom code (or if you need it regularly) I would strongly recommend using a stored procedure with parameters.
如果您正在从代码执行INSERT/ UPDATE（或者如果您经常需要它），我强烈建议您使用带参数的存储过程。
You could extend the procedure further by adding an INSERTblock to the procedure using an IF-ELSEto determine whether to execute INSERTnew record or UPDATE an existing, as seen in thisSO answer.
您可以通过使用 向过程添加一个INSERT块来进一步扩展过程，IF-ELSE以确定是执行INSERT新记录还是更新现有记录，如thisSO answer所示。
Finally, take a look at SQLFiddlefor a sandbox playground to test your SQL without risk to your RDMS :-)
最后，看看SQLFiddle的沙箱游乐场来测试您的 SQL 而不会对您的 RDMS 造成风险:-)
