MySQL INSERT ON DUPLICATE KEY UPDATE

In this tutorial, we are going to see how to use MySQL INSERT ON DUPLICATE KEY UPDATE statement to update data if a copy of UNIQUE index or PRIMARY KEY error occurs when you insert a row into a table.
 

Example of INSERT ON DUPLICATE KEY UPDATE statement

Let’s take an example of using INSERT ON DUPLICATE KEY UPDATE statement to see how it works. To do this we will use the “Contact” table.

CREATE TABLE Contact (id INT PRIMARY KEY, email VARCHAR(30));

INSERT INTO contact VALUES (1,'[email protected]') 
                           (2,'[email protected]'),
                           (3,'[email protected]');

 

 
If we execute an INSERT query with a primary key value equal to 1 will fail, due to the existing key:

INSERT INTO Contact VALUES (1,'[email protected]');

 
However, we can use the INSERT ON DUPLICATE KEY UPDATE statement as follows:

INSERT INTO Contact VALUES (1,'[email protected]') 
ON DUPLICATE KEY UPDATE email='[email protected]';

 
Note that there are two lines marked as affected, since the line with ID 1 already exists in the contact table, the above command updates the email “[email protected]” to “[email protected]”.
 

 

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More