MySQL INSERT ON DUPLICATE KEY UPDATE Statement

Summary: in this tutorial, you will learn how to use MySQL INSERT ON DUPLICATE KEY UPDATE statement to insert data into a table or update data if a duplicate key violation error occurs.

Note that this tutorial is relevant to MySQL 8.0.19 or later.

Introduction to the MySQL INSERT ON DUPLICATE KEY UPDATE statement

In MySQL, the INSERT ON DUPLICATE KEY UPDATE statement allows you to insert new rows into a table.

If a duplicate key violation occurs, you can use the INSERT ON DUPLICATE KEY UPDATE statement to update existing rows instead of throwing an error.

This INSERT ON DUPLICATE KEY UPDATE statement is useful when you deal with unique constraints or primary keys.

Here’s the syntax of INSERT ON DUPLICATE KEY UPDATE statement:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
   column1 = new_value1, 
   column2 = new_value2, 
   ...;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• INSERT INTO table_name: Specify the table name after the INSERT INTO keywords.
• (column1, column2, ...): List the columns in the table where you want to insert data
• VALUES(...): Provide values to be inserted into the corresponding columns
• ON DUPLICATE KEY UPDATE: Specify the action to take if a duplicate key violation occurs.
• column1 = new_value1, column2=new_value2: Define how existing rows should be updated if a duplicate key is encountered.

The statement returns the number of affected rows based on the action it performs:

• 1 is returned when a new row is inserted.
• 2 is returned when an existing row is updated.
• 0 is returned when no changes are made to an existing row.

Row aliases

MySQL allows you to define a row alias for the inserted row using the AS alias_name after the VALUES clause.

Then, you can use the alias within the ON DUPLICATE KEY UPDATE clause to reference the inserted row’s values.

Here’s the syntax for defining a row alias:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
AS new_data -- Row alias
ON DUPLICATE KEY UPDATE
  column1 = new_data.column1,
  column2 = new_data.column2 + 1;Code language: SQL (Structured Query Language) (sql)

Column aliases

MySQL also allows you to assign aliases to columns to avoid ambiguity, especially with long column names:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (...)
AS new(alias1, alias2, alias3, ...)
ON DUPLICATE KEY UPDATE
  column1 = alias2 + alias3;Code language: SQL (Structured Query Language) (sql)

MySQL INSERT ON DUPLICATE KEY UPDATE examples

Let’s take some examples of using the INSERT ON DUPLICATE KEY UPDATE to understand how it works.

We’ll create an employees table for the demonstration:

CREATE TABLE employees(
   id INT PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   age INT NOT NULL,
   salary DECIMAL(10,2) NOT NULL,
   bonus DECIMAL(10,2) DEFAULT 0
);Code language: SQL (Structured Query Language) (sql)

1) Using row alias example

First, insert a new row into the employees table:

INSERT INTO employees(id, name, age, salary)
VALUES(1, 'Jane Doe', 25, 120000);Code language: SQL (Structured Query Language) (sql)

Second, retrieve data from the employees table:

SELECT * FROM employees;Code language: SQL (Structured Query Language) (sql)

Output:

+----+----------+-----+-----------+-------+
| id | name     | age | salary    | bonus |
+----+----------+-----+-----------+-------+
|  1 | Jane Doe |  25 | 120000.00 |  0.00 |
+----+----------+-----+-----------+-------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Third, insert a new row into the employees table, or update the existing row if a duplicate key violation occurs:

INSERT INTO employees(id, name, age, salary)
VALUES (1, 'Jane Smith', 26, 130000)

AS new
ON DUPLICATE KEY UPDATE
   name = new.name,
   age = new.age,
   salary = new.salary;Code language: SQL (Structured Query Language) (sql)

Output:

Query OK, 2 rows affected (0.00 sec)Code language: CSS (css)

In this example, we use new as the row alias. Because the row with id 1 already exists, the statement updates the row instead of inserting a new one.

In the ON DUPLICATE KEY UPDATE clause, we access the new value specified in the VALUES clause via the row alias and use these new values to update the name, age, and salary column.

Finally, retrieve the data from the employees table:

SELECT * FROM employees;Code language: SQL (Structured Query Language) (sql)

Output:

+----+------------+-----+-----------+-------+
| id | name       | age | salary    | bonus |
+----+------------+-----+-----------+-------+
|  1 | Jane Smith |  26 | 130000.00 |  0.00 |
+----+------------+-----+-----------+-------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

2) Using MySQL INSERT ON DUPLICATE KEY UPDATE statement to update another column example

First, insert a new row or update the new salary and bonus column if the row exists:

INSERT INTO employees(id, name, age, salary)
VALUES(1, 'Jane Doe', 26, 140000)
AS new
ON DUPLICATE KEY UPDATE
   salary = new.salary,
   bonus = new.salary * 0.1;Code language: SQL (Structured Query Language) (sql)

Output:

Query OK, 2 rows affected (0.01 sec)Code language: CSS (css)

Second, retrieve data from the employees table:

SELECT * FROM employees;Code language: SQL (Structured Query Language) (sql)

Output:

+----+------------+-----+-----------+----------+
| id | name       | age | salary    | bonus    |
+----+------------+-----+-----------+----------+
|  1 | Jane Smith |  26 | 140000.00 | 14000.00 |
+----+------------+-----+-----------+----------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Summary

• Use the ON DUPLICATE KEY UPDATE option of the INSERT statement to insert data into a table and update existing data if a duplicate error occurs.