Web Design & Print Design Team - Array Studio

MySQL insert a new row or update old one

Posted By Slobodan Kovacevic on February 16, 2007

Today I stumbled upon a newish MySQL feature that can often be very useful. Since MySQL 4.1 there’s a non-standard feature (i.e. it’s an extension of SQL standard and won’t work on other databases) that lets you insert a new row, but if it happens that a row with same primary/unique key already exists it will just update that row.

Insert query syntax looks like this:

If there is already a row with primarykeycol set to 1 this query is equal to:

UPDATE table SET col1=0, col2=col2+1 WHERE primarykeycol = 1

Ordinarily to achieve the same result you would have to issue an UPDATE query, then check if there were affected rows and if not issue an INSERT query. This way, you can do everything in one step – first try insert and then update if insert fails.

One situation for which this type of syntax is perfect is when you work with daily counters. For example, you might have a table with PostID, Date and Count columns. Each day you’d have to check if you already created an entry for that day and if so increase the count column – and this can be easily substituted with one INSERT … ON DUPLICATE KEY UPDATE query.

Unfortunately there are some caveats. One being that when you have multiple unique indexes it will act as if you had an OR condition in WHERE clause of UPDATE query. This means that multiple rows should be update, but INSERT … ON DUPLICATE KEY UPDATE will update only one row.

For more information you should read article in MySQL manual: INSERT … ON DUPLICATE KEY UPDATE Syntax

Category: Programming, Resources and Links | 13 Comments »
Tags: MySQL, MySQL Insert Row, MySQL Row Update

About the author

Slobodan Kovacevic