MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query

Question

Hello All.

I have a sql query where I want to insert multiple rows in single query. so I used something like:

$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), for eg. 'name' in above case, should be checked and if such a 'name' already exists, the corresponding whole row should be updated otherwise inserted.

For instance, in below eg., if 'Katrina' is already present in database, the whole row, irrespective of number of fields, should be updated. Again if 'Samia' is not present, the row should be inserted.

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 how to proceed. I have multiple rows to insert/update at a time. Please give me the direction. Thanks.

Answer 1

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

Answer 2

Documentation mentions VALUES to refer to new 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),
     ...