MySQL ON DUPLICATE KEY UPDATE 在单个查询中插入多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2714587/
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 ON DUPLICATE KEY UPDATE for multiple rows insert in single query
提问by Prashant
I have a SQL query where I want to insert multiple rows in single query. so I used something like:
我有一个 SQL 查询，我想在单个查询中插入多行。所以我使用了类似的东西：
$sql = "INSERT INTO beautiful (name, age)
VALUES
('Helen', 24),
('Katrina', 21),
('Samia', 22),
('Hui Ling', 25),
('Yumie', 29)";
mysql_query( $sql, $conn );
The problem is when I execute this query, I want to check whether a UNIQUEkey (which is not the PRIMARY KEY), e.g. 'name'above, should be checked and if such a 'name'already exists, the corresponding whole row should be updated otherwise inserted.
问题是当我执行这个查询时，我想检查是否应该检查一个UNIQUE键（不是PRIMARY KEY），例如'name'上面的，如果这样一个'name'已经存在，则应该更新相应的整行，否则插入。
For instance, in the example below, if 'Katrina'is already present in the database, the whole row, irrespective of the number of fields, should be updated. Again if 'Samia'is not present, the row should be inserted.
例如，在下面的示例中，如果'Katrina'数据库中已经存在，则无论字段数量如何，都应该更新整行。同样，如果'Samia'不存在，则应插入该行。
I thought of using:
我想过使用：
INSERT INTO beautiful (name, age)
VALUES
('Helen', 24),
('Katrina', 21),
('Samia', 22),
('Hui Ling', 25),
('Yumie', 29) ON DUPLICATE KEY UPDATE
Here is the trap. I got stuck and confused about how to proceed. I have multiple rows to insert/update at a time. Please give me a direction. Thanks.
这里是陷阱。我被卡住了，对如何继续感到困惑。我一次要插入/更新多行。请给我一个方向。谢谢。
回答by Peter Lang
Use keyword VALUESto refer to new values (see documentation).
使用关键字VALUES来引用新值（请参阅文档）。
INSERT INTO beautiful (name, age)
VALUES
('Helen', 24),
('Katrina', 21),
('Samia', 22),
('Hui Ling', 25),
('Yumie', 29)
ON DUPLICATE KEY UPDATE
age = VALUES(age),
...
回答by li rachel
INSERT INTO ... ON DUPLICATE KEY UPDATE will only work for MYSQL, not for SQL Server.
INSERT INTO ... ON DUPLICATE KEY UPDATE 仅适用于 MYSQL，不适用于 SQL Server。
for SQL server, the way to work around this is to first declare a temp table, insert value to that temp table, and then use MERGE
对于 SQL Server，解决此问题的方法是首先声明一个临时表，将值插入该临时表，然后使用 MERGE
Like this:
像这样：
declare @Source table
(
name varchar(30),
age decimal(23,0)
)
insert into @Source VALUES
('Helen', 24),
('Katrina', 21),
('Samia', 22),
('Hui Ling', 25),
('Yumie', 29);
MERGE beautiful AS Tg
using @source as Sc
on tg.namet=sc.name
when matched then update
set tg.age=sc.age
when not matched then
insert (name, age) VALUES
(SC.name, sc.age);
