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.