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?? ;)