How To Insert or Update In MySQL Using ON DUPLICATE KEY UPDATE?

Summary: this tutorial shows you how to use MySQL INSERT ON DUPLICATE KEY UPDATE statement to insert or update data in a table if a duplicate unique key or primary key occurs.

Introduction to the MySQL INSERT ON DUPLICATE KEY UPDATE statement

The INSERT ON DUPLICATE KEY UPDATE is a MySQL extension to the INSERT statement. If you specify the ON DUPLICATE KEY UPDATE option in the INSERT statement and the new row causes a duplicate value in the UNIQUE or PRIMARY KEY index, MySQL performs an update to the old row based on the new values.

The syntax of INSERT ON DUPLICATE KEY UPDATE statement is as follows:

1 2 3

INSERT INTO table(column_list) VALUES(value_list) ON DUPLICATE KEY UPDATE column_1 = new_value_1, column_2 = new_value_2, …;

The only addition to the INSERT statement is the ON DUPLICATE KEY UPDATE clause where you specify a list of comma-separated column assignments.

MySQL returns the number of affected rows based on the action it performed.

1. If MySQL inserts the row as a new row, the number of affected row is 1.
2. If MySQL updates the current row, the number of affected rows is 2.
3. In case MySQL updates the current row with its current values, the number of affected rows is 0.

To reuse the values of the INSERT part in the DUPLICATE KEY UPDATE clause, you use the VALUES()function.

1 2 3

INSERT INTO sample_table(column_1) VALUES(column_1) ON DUPLICATE KEY UPDATE column_1 = VALUES(column_1) + 1;

The statement above sets the  value of the column_1 to its current value specified by the expression VALUES(column_1) + 1 if there is a duplicate in UNIQUE or PRIMARY KEY.

MySQL INSERT ON DUPLICATE KEY UPDATE example

Let’s take a look at an example of using the INSERT ON DUPLICATE KEY UPDATE statement to have a better understanding how it works.

First, create a table named devices to store the network devices.

1 2 3 4

CREATE TABLE devices (     id INT AUTO_INCREMENT PRIMARY KEY,     name VARCHAR(100) );

Next, insert rows into the devices table.

1 2	INSERT INTO devices(name) VALUES('Router F1'),('Switch 1'),('Switch 2');

Then, query the data from the devices table to verify the insert operation.

1 2 3 4

SELECT     id, name FROM     devices;

Now, we have 3 rows in the devices table.

After that, insert one more row into the devices table.

1 2	INSERT INTO devices(name) VALUES ('Printer') ON DUPLICATE KEY UPDATE name = 'Printer';

Because there is no duplicate, MySQL inserts a new row into the devices table. The statement above is like:

1	INSERT INTO devices(name) VALUES ('Printer');

Finally, insert a row with a duplicate value in id column.

1 2	INSERT INTO devices(id,name) VALUES (4,'Printer') ON DUPLICATE KEY UPDATE name = 'Server';

Because the row with id 4 already exists in the devices table, the statement updates the name from Printer to Server.

In this tutorial, you have learned how to insert or update data in a table using the ON DUPLICATE KEY UPDATE option of the INSERT statement.