I had some weirdness with MySQL today… Updating on a duplicate key error works like a charm: it updates the row which violates with the key and everybody is happy!
Well, actually I’m not… I encountered this problem today. I had a table with a number and an average and the table named averages looked like this:
|-----------------------------|
| id | number | average |
|-----------------------------|
| 1 | 1 | 0 |
| 2 | 4 | 0.25 |
| 3 | 2 | 0.5 |
|-----------------------------|
Now let’s say I wanted to increment id 1 with number=2 and an average=1, normally I would use the following query:
UPDATE `averages` SET `number`=`number`+2, `average`=(((`average`*`number`)+(1*2))/(`number`+2))
WHERE `id`='1';
This would update the table and set the number to 3 and average to 0.6666667. This worked fine till I added this to a cronjob which calculates averages from large quantities of rows. Since I would not know the identifiers upfront I changed the query to an INSERT query with a ON DUPLICATE KEY UPDATE part which updates when we already have a row for the identifier, so I came up with this:
INSERT INTO `averages` VALUES (1, 2, 1) ON DUPLICATE KEY UPDATE `number`=`number`+2,
`average`=(((`average`*`number`)+(1*2))/(`number`+2))
But, as I found out later, this doesn’t work the same way as the UPDATE query: the ON DUPLICATE KEY UPDATE does not write itself to a single UPDATE query, but rather to two seperate UPDATE queries. This means that the query will be written to this:
UPDATE `averages` SET `number`=`number`+2 WHERE `id`='1';
UPDATE SET `average`=(((`average`*`number`)+(1*2))/(`number`+2)) WHERE `id`='1';
Can you identify the problem I encountered here? And perhaps guess what the average column was set to?? ;)
