INSERT ... ON DUPLICATE KEY UPDATE

Grammar Description

INSERT ... ON DUPLICATE KEY UPDATE is used to update data if it already exists when inserting data into a database table, otherwise new data is inserted.

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

It should be noted that the prerequisite for using this syntax is to establish primary key constraints 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 it will cause syntax errors.

Grammar Structure

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

Example

CREATE TABLE user (
    id INT(11) NOT NULL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT(3) NOT NULL
);
-- Insert a new data, the id does not exist, so enter the new data
INSERT INTO user (id, name, age) VALUES (1, 'Tom', 18)
ON DUPLICATE KEY UPDATE name='Tom', age=18;

mysql> select * from user;
+------+------+
| id | name | age |
+------+------+
| 1 | Tom | 18 |
+------+------+
1 row in set (0.01 sec)

-- Increase the age field of an existing record by 1, while the name field remains unchanged
INSERT INTO user (id, name, age) VALUES (1, 'Tom', 18)
ON DUPLICATE KEY UPDATE age=age+1;

mysql> select * from user;
+------+------+
| id | name | age |
+------+------+
| 1 | Tom | 19 |
+------+------+
1 row in set (0.00 sec)

-- Insert a new record and update the name and age fields to the specified value
INSERT INTO user (id, name, age) VALUES (2, 'Lucy', 20)
ON DUPLICATE KEY UPDATE name='Lucy', age=20;

mysql> select * from user;
+------+------+
| id | name | age |
+------+------+
| 1 | Tom | 19 |
| 2 | Lucy | 20 |
+------+------+
2 rows in set (0.01 sec)

limit

INSERT ... ON DUPLICATE KEY UPDATE Unique key is not currently supported, because the unique key can be NULL, some unknown errors may occur.