MySQL的重复密钥更新，用于在单个查询中插入多个行

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 UNIQUE key (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.

问题是，当我执行这个查询时，我想检查一个唯一的键(不是主键)，例如。上面的'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.

这是陷阱。我对如何继续下去感到困惑和困惑。我一次要插入/更新多个行。请给我指路。谢谢。

3 个解决方案

#1

365

Use keyword VALUES to refer to new values (see documentation).

使用关键字值来引用新值(参见文档)。

INSERT INTO beautiful (name, age)
    VALUES
    ('Helen', 24),
    ('Katrina', 21),
    ('Samia', 22),
    ('Hui Ling', 25),
    ('Yumie', 29)
ON DUPLICATE KEY UPDATE
    age = VALUES(age),
     ...

#2

You can use Replace instead of INSERT ... ON DUPLICATE KEY UPDATE.

你可以用Replace代替INSERT…在重复键更新。

#3

INSERT INTO ... ON DUPLICATE KEY UPDATE will only work for MYSQL, not for SQL Server.

插入……对于重复的密钥更新只适用于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);

#1

365