MySQL hackery: ON DUPLICATE KEY with IF!
October 15th, 2009 by ravi

Once again, since the Googles did not return this hack of mine as a possible answer (to a question that perhaps only I am concerned with):

Say you want to insert a row into a MySQL table, and if the value you supply for a unique key field already exists, then you just want to update the row that contains that value (for that key field). MySQL has a nifty little bit called ON DUPLICATE KEY UPDATE which lets you do exactly this. But what if you want to take it to 11, and carry out the UPDATE only if some condition is true. Below is my somewhat ugly hack to achieve this:

What this does is:

INSERTS a new row if no unique key has a duplicate value.

If there is such a duplicate value, then it UPDATEs the field fld2 with the supplied value for fld2 IF the supplied value for fld3 is greater than the current value for fld3 in the existing row.

Here, id is an auto-increment field that I do not want modified by the UPDATE; hence the LAST_INSERT_ID trick.

CAUTION: Use at your own risk, since I am not an SQL guru.

»  Substance: WordPress  »  Style: Ahren Ahimsa