MySQL – Conditional Updates

Just a quick blog post in an update related way,

Recently i’ve needed to toggle multiple rows in a database from 1 value to 1 of possible 2 values dependant on the contents of a field on the row. I wasn’t sure if this was possible, so i asked in #PHPWomen IRC Channel and the general response was that of “Don’t Know”.

So After a backup of the table in question, i decided to try it and see. After all, the worst it could do was just not work. So I tried the following:

UPDATE `tble_name` SET `field1`=IF(`field2`='arbitrary_value',1,0) WHERE `field3`='other_value'

And Voila updated all the rows exactly as needed.

So, It is possible to update all rows in a table, limited to a group and update value based upon a field matching a certain Value.
So all in all MySQL For the WIN!!!