MySQL Insert On Duplicate Key Update

The MySQL can use the INSERT ... ON DUPLICATE KEY UPDATE statement to insert a new record into a table. If a duplicate key violation occurs, meaning that the new record has a primary key or unique constraint value that already exists in the table, the statement will update the existing record with the new values instead of inserting a duplicate. This is a way to handle conflicting records and ensure data integrity.

The basic syntax for 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, ...;

Here’s a breakdown of each part of the statement:

• table_name: This is the name of the table into which you want to insert or update data.
• (column1, column2, ...): You specify the columns into which you want to insert data in the table.
• VALUES (value1, value2, ...): You provide the values you want to insert into the specified columns.
• ON DUPLICATE KEY UPDATE: This part of the statement indicates that if there’s a duplicate key violation, the subsequent part of the statement should be executed.
• column1 = value1, column2 = value2, ...: In the event of a duplicate key violation, you specify which columns should be updated with which values.

Here’s an example of how to use the INSERT ... ON DUPLICATE KEY UPDATE statement:

Suppose you have a table called employees with a primary key employee_id and you want to insert a new employee record. If a record with the same employee_id already exists, you want to update the employee’s information. If not, you want to insert a new record.

INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (101, 'John', 'Doe', 50000)
ON DUPLICATE KEY UPDATE
first_name = VALUES(first_name), last_name = VALUES(last_name), salary = VALUES(salary);

In this example, if a record with employee_id 101 already exists, the ON DUPLICATE KEY UPDATE clause will update the first_name, last_name, and salary of the existing record with the values specified. If no duplicate key violation occurs, a new record will be inserted with the provided values.

This statement is particularly useful when dealing with scenarios where you want to insert new records or update existing records in a single operation, making it a convenient way to handle data upserts.