Open In App

INSERT ON DUPLICATE KEY UPDATE in MySQL

Improve
Improve
Like Article
Like
Save
Share
Report

INSERT ON DUPLICATE KEY UPDATE statement is available in MySQL as an extension to the INSERT statement. Whenever a new row is inserted into a table in case the row causes a duplicate entry in the UNIQUE index or PRIMARY KEY, MySQL will throw an error.

When the ON DUPLICATE KEY UPDATE option is defined in the INSERT statement, the existing rows are updated with the new values instead.

Syntax :

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 duplicate.

How it works :
The statement first attempts to insert a new row into the table. When a duplicate entry is their error occurs, MySQL will update the existing row with the value specified in the ON DUPLICATE KEY UPDATE clause.

Example –
Let us create a table named ‘geek_demo’ as follows.

CREATE TABLE 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 :

id name
1 Neha
2 Nisha
3 Sara

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 :

id name
1 Neha
2 Nisha
3 Sara
4 Sneha

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 :

id name
1 Neha
2 Nisha
3 Sara
4 Mona

Last Updated : 23 Dec, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads