I tend to use a lot of tinyint columns as controllers for my database-held objects, usually sticking to the convention of 1 means on and 0 means off. In other words, the perfect target for a SQL toggle statement!

In order to toggle update a value in SQL, we’ll need to make use of the common SQL control flow function IF. The IF function takes three parameters, the first being the test expression, the second being the term to return if the test expression passes and the third being the term returned if the test expression fails.

So putting this into our 1 or 0 toggle SQL statement, we get:

UPDATE `table` SET `column` = IF(`column` = 1, 0, 1) WHERE `id` = x

It’s pretty intuitive to see what is going on above, now that we understand the form of the fabulous IF SQL function!


Related Link: http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html#function_if