带有 WHERE 子句的 MySQL 直接 INSERT INTO
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12948554/
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
MySQL direct INSERT INTO with WHERE clause
提问by Hasitha Shan
I tried googling for this issue but only find how to do it using two tables, as follows,
我尝试谷歌搜索这个问题，但只找到如何使用两个表来做，如下所示，
INSERT INTO tbl_member
SELECT Field1,Field2,Field3,...
FROM temp_table
WHERE NOT EXISTS(SELECT *
FROM tbl_member
WHERE (temp_table.Field1=tbl_member.Field1 and
temp_table.Field2=tbl_member.Field2...etc.)
)
This worked for one scenario,But now my interest is to upload data directly from the program itself without using two tables. What i want is to upload the data which is not in the table. The sql i had in my head was like the following,
这适用于一种情况，但现在我的兴趣是直接从程序本身上传数据而不使用两个表。我想要的是上传不在表格中的数据。我脑子里的 sql 如下所示，
INSERT INTO tbl_member (SensorIdValue, DataTimeValue, DataInValue, IncompleteValue, SpiValue, InfoValue)
VALUES ('Sensor.org', '20121017150103', 'eth0','','','')
WHERE (SensorIdValue != 'Sensor.org'AND DataTimeValue != '20121017150103'AND DataInValue != 'eth0'AND IncompleteValue != ''AND SpiValue != ''AND InfoValue != '');
But it's wrong.. may i know the proper way of doing it please, Thank you very much :)
但这是错误的..我可以知道正确的方法吗，非常感谢:)
回答by John Woo
INSERTsyntax cannot have WHEREclause. The only time you will find INSERThas WHEREclause is when you are using INSERT INTO...SELECTstatement.
INSERT语法不能有WHERE子句。只有在使用语句时才会找到INSERThasWHERE子句INSERT INTO...SELECT。
The first syntax is already correct.
第一个语法已经正确。
回答by Rj Raawat
you can use UPDATE command.
您可以使用 UPDATE 命令。
UPDATE table_name SET name=@name, email=@email, phone=@phone WHERE client_id=@client_id
回答by Pomster
Example of how to perform a INSERT INTO SELECT with a WHERE clause.
如何使用 WHERE 子句执行 INSERT INTO SELECT 的示例。
INSERT INTO #test2 (id) SELECT id FROM #test1 WHERE id > 2
回答by user1286858
If I understand the goal is to insert a new record to a table but if the data is already on the table: skip it! Here is my answer:
如果我理解目标是向表中插入一条新记录，但如果数据已经在表中：跳过它！这是我的回答：
INSERT INTO tbl_member
(Field1,Field2,Field3,...)
SELECT a.Field1,a.Field2,a.Field3,...
FROM (SELECT Field1 = [NewValueField1], Field2 = [NewValueField2], Field3 = [NewValueField3], ...) AS a
LEFT JOIN tbl_member AS b
ON a.Field1 = b.Field1
WHERE b.Field1 IS NULL
The record to be inserted is in the new value fields.
要插入的记录位于新值字段中。
回答by hoang nguyen
The INSERT INTO Statement
The INSERT INTO statement is used to insert a new row in a table.
SQL INSERT INTO Syntax
INSERT INTO 语句
INSERT INTO 语句用于在表中插入新行。
SQL INSERT INTO 语法
It is possible to write the INSERT INTO statement in two forms.
可以用两种形式编写 INSERT INTO 语句。
The first form doesn't specify the column names where the data will be inserted, only their values:
第一种形式不指定将插入数据的列名，只指定它们的值：
INSERT INTO table_name
VALUES (value1, value2, value3,...)
The second form specifies both the column names and the values to be inserted:
第二种形式指定要插入的列名和值：
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
