UPON DUPLICATE KEY increment multiple columns?

Question

Im running a database log and every day I log on a new row. My Mysql query therefore checks if the day (date (the unique key)) already exists, and if so, it tries to increment all the loggable values of the log-row by one. If the date record doesnt eyist yet, it will create a new row.

My SQL query is:

INSERT INTO `log` (`date`,`hits`,`stale`)
VALUES ('2012-03-06',1,1)   
ON DUPLICATE KEY
UPDATE `hits`=`hits`+1,`stale`=`stale`+1
WHERE `date`='2012-03-06';"

All columns have 0 as default value, so if this query runs directly after midnight only 'stale' and 'hits' are set to 1. Otherwise 'stale' and 'hits' are both incremented.

I wish! (it doesn't work).

What am I missing? Which separator other then a comma should I use between 'hits' = 'hits' +1 and 'stale'='stale'+1?

Answer 1

Your separator is correct, but the UPDATE has already found the duplicate row to be able to trigger the ON DUPLICATE KEY, so you don't need to try to select it again using WHERE.

INSERT INTO `log` (`date`,`hits`,`stale`)
VALUES ('2012-03-06',1,1)   
ON DUPLICATE KEY
UPDATE `hits`=`hits`+1,`stale`=`stale`+1

Demo here.

Answer 2

Just get rid of the WHERE clause:

INSERT INTO `log` (`date`,`hits`,`stale`)
VALUES ('2012-03-06',1,1)   
ON DUPLICATE KEY
UPDATE `hits`=`hits`+1,`stale`=`stale`+1;

Answer 3

If you only want to do the update if some specific expression is true, you can do it with two statements:

INSERT IGNORE INTO x VALUES (.....);
UPDATE x SET ..... WHERE .....;

The INSERT will silently fail if there is a duplicate key.

Answer 4

You shouldn't have the WHERE clause. ON DUPLICATE KEY UPDATE automatically limits the row it affects to the one that has the existing key.

Remove it and your query should work fine.