A simple method to toggle a bit column (MySQL, SQL Server, and others)

I’ll occasionally see SQL code that looks something like this:

UPDATE products SET
   in_stock = CASE WHEN in_stock = 1 THEN 0 ELSE 1 END;

Or sometimes the developer tries to accomplish the same thing with an overly complex subquery. We can greatly simplify what we’re trying to do with just this query:

UPDATE products SET in_stock =  in_stock ^ 1;

The trick is using the caret (“^”), which is the bitwise XOR operator (MySQL, MS SQL Server) with either a “0” or a “1”. For those of you who’s binary math is a bit rusty, remember:

1 ^ 1 = 0
0 ^ 0 = 0
1 ^ 0 = 1
0 ^ 1 = 1

As you can see, the XOR operator returns “0” if the two arguments match and “1” if they don’t. This makes it easy to flip the bit in a boolean column without having to first check it’s existing value.

2 Replies to “A simple method to toggle a bit column (MySQL, SQL Server, and others)”

  1. Yup. Works great in MySQL… not so much in MS SQL Server though. 😉 The bit flip trick has worked with every DBMS I’ve tried it with throughout the years (though Postgres uses “#” instead of “^”). Please let me know if anyone comes across a platform it doesn’t work on.

Comments are closed.