MySQL INSERT ON DUPLICATE KEY UPDATE

The Insert on Duplicate Key Update statement is the extension of the INSERT statement in MySQL. When we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updation of the existing row occurs.

In other words, when we insert new values into the table, and it causes duplicate row in a UNIQUE OR PRIMARY KEY column, we will get an error message. However, if we use ON DUPLICATE KEY UPDATE clause in a SQL statement, it will update the old row with the new row values, whether it has a unique or primary key column.

For example, if column col1 is defined as UNIQUE and contains the value 10 into the table tab1, we will get a similar effect after executing the below two statements:

It makes sure that if the inserted row matched with more than one unique index into the table, then the ON DUPLICATE KEY statement only updates the first matched unique index. Therefore, it is not recommended to use this statement on tables that contain more than one unique index.

If the table contains AUTO_INCREMENT primary key column and the ON DUPLICATE KEY statement tries to insert or update a row, the Last_Insert_ID() function returns its AUTO_INCREMENT value.

The following are the syntax of Insert on Duplicate Key Update statement in MySQL:

In this syntax, we can see that the INSERT statement only adds the ON DUPLICATE KEY UPDATE clause with a column-value pair assignment whenever it finds duplicate rows. The working of ON DUPLICATE KEY UPDATE clause first tries to insert the new values into the row, and if an error occurs, it will update the existing row with the new row values.

The VALUES() function only used in this clause, and it does not have any meaning in any other context. It returns the column values from the INSERT portion and particularly useful for multi-rows inserts.

MySQL gives the number of affected-rows with ON DUPLICATE KEY UPDATE statement based on the given action:

• If we insert the new row into a table, it returns one affected-rows.
• If we update the existing row into a table, it returns two affected-rows.
• If we update the existing row using its current values into the table, it returns the number of affected-rows 0.

MySQL INSERT ON DUPLICATE KEY Example

Let us understand the working of the INSERT ON DUPLICATE KEY UPDATE clause in MySQL with the help of an example.

First, create a table named "Student" using the below statement:

Next, insert the data into the table. Execute the following statement:

Execute the SELECT statement to verify the insert operation:

We will get the output as below where we have three rows into the table:

Again, add one more row into the table using the below query:

The above statement will add row successfully because it does not have any duplicate values.

Finally, we are going to add a row with a duplicate value in the Stud_ID column:

MySQL gives the following message after successful execution of the above query:

In the below out, we can see that the row id=4 already exists. So the query only updates the City New York with California.