MySQL - on duplicate key update
If you’ve ever built an application that interacts with a database and
allows users to edit data, or one that migrates data from one system to
another you’ve probably written code like this:
Well, thanks to the ON DUPLICATE KEY UPDATE option in MySQL this can be
condensed to the following:
- You don’t have to update every field on update. If you only need one
row updated if the row already exists, specify only that row in the
UPDATE portion of the query.
- I prefer the above syntax (using set and writing field = value for
each field) but as shown in the MySQL
it works with the (field1,field2,field3) VALUES
(value1,value2,value3) version as well.
- This method does not work perfectly in all situations! Be sure to
test on your dev server before using in production!