ON DUPLICATE KEY UPDATE clause in MySQL

The ON DUPLICATE KEY UPDATE a clause in MySQL updates the existing record if a duplicate critical violation occurs when inserting a new record. This is particularly useful when you want to insert a new record. Still, if a form with the same key already exists, you want to update specific columns of the existing record instead of getting an error.

Here’s a basic example to illustrate its usage:

Suppose you have a table named users with columns id, name, and age. The id column is a unique key.

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT
);

Now, let’s say you want to insert a new user with id=1, name='John', and age=25. But if a user with id=1 already exists, you want to update the name and age columns.

You can achieve this using the ON DUPLICATE KEY UPDATE clause:

INSERT INTO users (id, name, age)
VALUES (1, 'John', 25)
ON DUPLICATE KEY UPDATE name='John', age=25;

In the above SQL statement:

• If a record with id=1 doesn’t exist, a new record will be inserted with the provided values.
• If a record with id=1 already exists, the name and age columns of the existing record will be updated to ‘John’ and 25, respectively.

You can also use the VALUES() Function to refer to the value that would have been inserted:

INSERT INTO users (id, name, age)
VALUES (1, 'John', 25)
ON DUPLICATE KEY UPDATE name=VALUES(name), age=VALUES(age);

In this case, the VALUES(name) refers to ‘John’ and VALUES(age) refers to 25. This is useful when you’re inserting multiple rows at once and want to use the ON DUPLICATE KEY UPDATE clause for each row.

Remember to ensure that the column you’re checking for duplicates (in this case id) has a unique constraint or is a primary key. Otherwise, the ON DUPLICATE KEY UPDATE clause won’t have any effect.

INSERT ... VALUES ... AS alias syntax

In MySQL 8.0 and later, you can use the INSERT ... VALUES ... AS alias syntax in combination with the ON DUPLICATE KEY UPDATE clause. This allows you to refer to the values being inserted utilizing an alias, which can be especially useful when performing operations based on the new values without using the VALUES() function.

Here’s how you can use this syntax:

Suppose you have the same users Table:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT
);

You want to insert a new user with id=1, name='John', and age=25. If a user with id=1 already exists, you want to update the name and age columns based on the new values.

Using the INSERT ... VALUES ... AS alias syntax:

INSERT INTO users (id, name, age)
VALUES (1, 'John', 25) AS new
ON DUPLICATE KEY UPDATE name=new.name, age=new.age;

In the above SQL:

• The AS new gives an alias to the values being inserted.
• In the ON DUPLICATE KEY UPDATE clause, you can refer to the values being inserted using the new alias, like new.name and new.age.

This approach provides a clear and concise way to refer to the values being inserted, especially when you’re dealing with multiple columns or more complex operations in the ON DUPLICATE KEY UPDATE clause.

INSERT INTO SELECT clause

If you’re using a SELECT clause to provide the values for the INSERT statement, you can still use the AS alias syntax to refer to those values in the ON DUPLICATE KEY UPDATE clause.

Here’s an example to illustrate:

Suppose you have two tables: users and new_users_data. You want to insert data from the new_users_data table into the users table. If a duplicate key is found, you want to update the users table based on the values from the new_users_data table.

-- Sample table structures
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT
);

CREATE TABLE new_users_data (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT
);

Using the INSERT ... SELECT ... AS alias syntax:

INSERT INTO users (id, name, age)
SELECT id, name, age FROM new_users_data AS new
ON DUPLICATE KEY UPDATE name=new.name, age=new.age;

In this SQL:

• The SELECT statement fetches data from the new_users_data table and assigns it an alias new.
• In the ON DUPLICATE KEY UPDATE clause, you can refer to the values from the new_users_data table using the new alias, like new.name and new.age.

This approach allows you to seamlessly integrate data from another table or a subquery and handle duplicate critical scenarios efficiently.

How To Get MySQL Version