MySQL INSERT ON DUPLICATE KEY UPDATE Statement

MySQL INSERT ON DUPLICATE KEY UPDATE statement is an extension to the INSERT statement, that if the row being inserted already exists in the table, it will perform a UPDATE operation instead.

INSERT ON DUPLICATE KEY UPDATE statement in MySQL is used to handle duplicate entries on a primary key or unique column. This statement works like a normal INSERT statement until a duplicate value is inserted in a primary key column.

When a duplicate value is entered in the primary key column, it raises an error. But the ON DUPLICATE KEY UPDATE clause handles this error by updating the row of the Primary key column.

MySQL INSERT ON DUPLICATE KEY UPDATE statement syntax is:

INSERT INTO table (column_names)VALUES (values)ON DUPLICATE KEY UPDATE col1 = val1, col2 = val2 ;

Along with the INSERT statement, ON DUPLICATE KEY UPDATE statement defines a list of column & value assignments in case of duplicates.

Let’s look at some examples of the INSERT ON DUPLICATE KEY UPDATE statement in SQL and understand how it works.

Let us create a table named ‘geek_demo’ as follows:

follows: geek_demo
(
id INT AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(100) 
);

Inserting data into geek_demo :

INSERT INTO geek_demo (name)
VALUES('Neha'), ('Nisha'), ('Sara') ;

Reading data from table :

SELECT id, name
FROM geek_demo;

Output :

Now, one row will insert into the table.

INSERT INTO geek_demo(name) 
VALUES ('Sneha') 
ON DUPLICATE KEY UPDATE name = 'Sneha';

As there was no duplicate, MySQL inserts a new row into the table. The output of the above statement is similar to the output below statement as follows.

INSERT INTO geek_demo(name)
VALUES ('Sneha');

Reading data :

SELECT id, name
FROM geek_demo; 

Output :

Let us insert a row with a duplicate value in the ID column as follows:

INSERT INTO geek_demo (id, name) 
VALUES (4, 'Mona')
ON DUPLICATE KEY UPDATE name = 'Mona';

Below is the output :

2 row(s) affected

Because a row with id 4 already exists in the geek_demo table, the statement updates the name from Sneha to Mona.

Reading data :

SELECT id, name
FROM geek_demo;

Output :

• The INSERT ON DUPLICATE KEY UPDATE statement in MySQL allows you to insert new rows into a table. If a duplicate key violation occurs, it updates existing rows instead of throwing an error.
• This feature is designed to be efficient and easy to use. It is more efficient than executing a separate SELECT statement followed by an INSERT or UPDATE.
• This statement is a convenient way to ensure data integrity.
• Use this statement with caution, as it might lead to unintended data overwrites if not used properly.