CODEWITHSUNDEEP
mysqlincrementon-duplicate-key
wallflux
wallflux

Reputation: 430

UPON DUPLICATE KEY increment multiple columns?

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?

Upvotes: 0

Views: 182

Answers (4)

Erik Ekman
Erik Ekman

Reputation: 2066

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.

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180897

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.

Upvotes: 1

Amber
Amber

Reputation: 526573

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.

Upvotes: 0

Ike Walker
Ike Walker

Reputation: 65537

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;

Upvotes: 1

Related Questions