INSERT … ON DUPLICATE KEY UPDATE

Description

INSERT ... ON DUPLICATE KEY UPDATE is used to insert data into the database table; if the data already exists, update the data; otherwise, insert new data.

The INSERT INTO statement is a standard statement used to insert data into a database table; the ON DUPLICATE KEY UPDATE statement performs an update operation when there are duplicate records in the table. If a record with the same unique index or primary key exists in the table, use the UPDATE clause to update the corresponding column value; otherwise, use the INSERT clause to insert a new record.

It should be noted that the premise of using this syntax is that a primary key constraint needs to be established in the table to determine whether there are duplicate records. At the same time, both the update operation and the insert operation need to set the corresponding column value. Otherwise, a syntax error will result.

Syntax

  1. > INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
  2.   [ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, column3 = value3, ...];

Examples

  1. CREATE TABLE user (
  2.     id INT(11) NOT NULL PRIMARY KEY,
  3.     name VARCHAR(50) NOT NULL,
  4.     age INT(3) NOT NULL
  5. );
  6. -- Insert a new data; it does not exist, insert the new data
  7. INSERT INTO user (id, name, age) VALUES (1, 'Tom', 18)
  8. ON DUPLICATE KEY UPDATE name='Tom', age=18;
  10. mysql> select * from user;
  11. +------+------+------+
  12. | id   | name | age  |
  13. +------+------+------+
  14. |    1 | Tom  |   18 |
  15. +------+------+------+
  16. 1 row in set (0.01 sec)
  18. -- Increment the age field of an existing record by 1 while keeping the name field unchanged
  19. INSERT INTO user (id, name, age) VALUES (1, 'Tom', 18)
  20. ON DUPLICATE KEY UPDATE age=age+1;
  22. mysql> select * from user;
  23. +------+------+------+
  24. | id   | name | age  |
  25. +------+------+------+
  26. |    1 | Tom  |   19 |
  27. +------+------+------+
  28. 1 row in set (0.00 sec)
  30. -- Insert a new record, and update the name and age fields to the specified values
  31. INSERT INTO user (id, name, age) VALUES (2, 'Lucy', 20)
  32. ON DUPLICATE KEY UPDATE name='Lucy', age=20;
  34. mysql> select * from user;
  35. +------+------+------+
  36. | id   | name | age  |
  37. +------+------+------+
  38. |    1 | Tom  |   19 |
  39. |    2 | Lucy |   20 |
  40. +------+------+------+
  41. 2 rows in set (0.01 sec)

Constraints

  1. INSERT ... ON DUPLICATE KEY UPDATE only supports 1 unique constraint and does not support multiple unique constraints.
  2. Unique key are not currently supported with INSERT ... ON DUPLICATE KEY UPDATE, and since unique key can be null, some unknown errors can occur.