MySQL INSERT ON DUPLICATE KEY UPDATE Examples
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 :
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 |
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.
Contact Us