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?

score 1 · Answer 1 · answered Mar 06 '12 at 19:31

1

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;

answered Mar 06 '12 at 19:31

Ike Walker

64,401
14
110
109

score 1 · Answer 2 · answered Mar 06 '12 at 19:35

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.

answered Mar 06 '12 at 19:35

Joachim Isaksson

176,943
25
281
294

Thanks! Somehow I thought it did work with only one increment and not with the second, but somwehere along the row I guess I added the WHERE clause... – wallflux Mar 06 '12 at 19:52

score 0 · Accepted Answer · answered Mar 06 '12 at 19:32

0

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.

answered Mar 06 '12 at 19:32

Amber

507,862
82
626
550

score 0 · Answer 4 · answered Mar 06 '12 at 22:00

0

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.

answered Mar 06 '12 at 22:00

Erik Ekman

2,051
12
13

UPON DUPLICATE KEY increment multiple columns?

4 Answers4

Linked