mySQL: on duplicate key update

March 7, 2018 by Josh Senyak

mySQL has a proprietary extension to the “insert” statement which is really handy in certain situations. It’s called ON DUPLICATE KEY UPDATE. If you want to update a table from a similar table, when there are possible duplicates, it updates just the way you’d want.

For example. Take a simple table “test” with five records:

Here’s a table “test2.” Maybe it’s a copy of the original table, but has had some data entry in the meantime.

We want to update the original table test with some of the new data from test2. But carefully!

Table test2 has two fields (other than the primary key testID), and there have been changes to both fields. Let’s say that we want to update table test with the changes to the field someText, but that we don’t trust (or don’t care about) changes to field someNumber.

Note also that test2 has a new record – with testID 6 – a record that the old test table doesn’t even know about yet.

So here’s the SQL to use:
insert into test(testID, someNumber, someText)
select testID, someNumber, someText from test2
on duplicate key update test.someText=test2.someText;


Here’s what test looks like as a result:

The INSERT statement worked just as requested. It tried to stick all the records from test2 into test. If the record already existed in test (as defined by primary key), it just updated the requested field. (It could have been any or all fields in test2 – we only asked for the someText field.) Or, if the record didn’t exist in test, the INSERT statement copied in the record wholesale.

By using an IF function you can control the update further. Let’s say you only want to update the someText field if the new data is longer than the old data. Use this:

insert into test(testID, someNumber, someText)
select testID, someNumber, someText from test2
on duplicate key update test.someText=
IF(length(test2.someText)>length(test.someText), test2.someText, test.someText) ;


You can get more detail (and some caveats) at the official mySQL documentation on insert-on-duplicate.