SQL: Toggle 1 and 0 Value in an Update SQL Statement CodeUnit 14 JAN 2011

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

About Craig Lotter

Software developer, husband and dad to two little girls. Writer behind An Exploring South African. I don't have time for myself any more.

  • Tomáš Kout

    Thank you a lot! That’s so clear. One tends to push everything through the script to vary a query, when this one line of code does it all!