MySQL : Upserting a row into database

It’s a common requirement to upsert (update if present, insert if not) a row in applications that have a database. This is not a big deal in NoSQL world, for example in Cassandra row will be inserted if you try to update a row that is not present. In MySQL there is no direct support for this behaviour.

MySQL provides `ON DUPLICATE KEY` clause that can be appended to a INSERT query. We can achieve upsert behaviour with this clause in specific scenarios. There are some limitations on tables that have auto-increment unique columns or more than one unique columns.

This is very useful for tables that have counters, for example if you have table that tracks number of visits for each page on a website, your application might have to check if the row is present for a given page before trying to increment the counter. Using `ON DUPLICATE KEY` we can avoid the extra select at application level.

CREATE TABLE `page_visists` ( `page_name` varchar(200), `visits` int, PRIMARY KEY (`page_name`));
INSERT INTO page_visists (page_name,visits) values ('page-1',1) ON DUPLICATE KEY UPDATE visits = visits+1;

You can find more about this clause on this page.