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.
What about logical not operator (!)?
UPDATE products SET in_stock = ! in_stock;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.