Insert On Duplicate Key Update (MySQL Queries)

In MySQL, the INSERT statement is used to insert new rows into a table. If the INSERT statement attempts to insert a new row that violates a unique key constraint, a Duplicate entry error is generated. In some cases, you may want to update an existing row with new values instead of generating an error when there's a duplicate entry. In such cases, you can use the INSERT ... ON DUPLICATE KEY UPDATE statement.

Syntax

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

INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,...)
ON DUPLICATE KEY UPDATE column1=value1, column2=value2,...;

The INSERT ... ON DUPLICATE KEY UPDATE statement consists of two parts:

• The first part is the INSERT INTO ... VALUES clause that indicates the new row to be inserted.
• The second part is the ON DUPLICATE KEY UPDATE clause that specifies what to update if a key already exists.

Example

Let's consider the following example:

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL UNIQUE
);

INSERT INTO users (id, name, email) VALUES
(1, 'John', 'john@example.com'),
(2, 'Jane', 'jane@example.com'),
(3, 'Bob', 'bob@example.com');

INSERT INTO users (id, name, email) VALUES
(4, 'Joe', 'joe@example.com')
ON DUPLICATE KEY UPDATE name = 'Joseph';

In this example, we first create a table called users with columns id, name, and email. The email column has a unique constraint.

We then insert three rows into the users table using the INSERT INTO ... VALUES statement.

Next, we insert a new row into the users table using the INSERT INTO ... ON DUPLICATE KEY UPDATE statement. In this case, the email value of joe@example.com already exists in the table. Instead of generating a Duplicate entry error, the ON DUPLICATE KEY UPDATE clause updates the name column of the existing row to Joseph.

Output

When we run the example code above, the output will be:

Query OK, 0 rows affected (0.01 sec)

Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.02 sec)

This output indicates that the table has been created, the three initial rows have been inserted, and the INSERT INTO ... ON DUPLICATE KEY UPDATE statement has updated one row.

Explanation

In the example above, we created a table called users with columns id, name, and email. We then inserted three rows into the table using the INSERT INTO ... VALUES statement.

We then used the INSERT INTO ... ON DUPLICATE KEY UPDATE statement to insert a new row into the users table. This statement updates the name column of an existing row with the email value of joe@example.com instead of generating a Duplicate entry error.

Use

The INSERT INTO ... ON DUPLICATE KEY UPDATE statement can be useful when you want to insert a new row into a table but also update an existing row if a unique constraint is violated. This statement saves you from having to write additional code to check if a row already exists before inserting a new row.

Important Points

• The INSERT INTO ... ON DUPLICATE KEY UPDATE statement is only available for tables with unique keys or primary keys defined.
• The ON DUPLICATE KEY UPDATE clause can update one or more columns in the row that violates a unique constraint.

Summary

In this tutorial, we discussed the INSERT INTO ... ON DUPLICATE KEY UPDATE statement in MySQL. We covered the syntax, example, output, explanation, use, and important points of this statement. With this knowledge, you can use the INSERT INTO ... ON DUPLICATE KEY UPDATE statement to insert new rows into a table and update existing rows with new values when a unique constraint is violated.