I found myself organizing my code snippets the other day. (Go ahead and poke fun at the Type A coder. I deserve it.) I have a folder dedicated to “SQL – Common Routines.” There within, I came across “Flip Bit.sql.” Over the years, I’ve come across quite a few ways to flip a bit in a boolean column so I was a little curious how this snippet would read.
Here’s what I know. To be honest, I’m not certain which of the four routines perform the best, but I’d put my money on # 3.
# 1. There is the brute force method. I like this method primarily because it is somewhat self-documenting and there is absolutely no question what the coder intends to do here.
update products set discontinued = case when discontinued = 1 then 0 else 1 end
# 2. The second option is to use the bitwise XOR operator with either a ‘0’ or a ‘1’. What does this do? The XOR operator returns ‘0’ if the two arguments match and ‘1’ if they don’t.
update product set discontinued = discontinued ^ 1
# 3. There is also the bitwise NOT operator. The NOT operator converts each bit in a numeric expression of integer data type into its complement. In simplest terms, ‘0’ become ‘1’ and vica versa.
update products set discontinued = ~discontinued
# 4. Finally, one may use MOD (which I surprisingly found in my snippet collection.)
update products set discontinued = ((discontinued + 1) % 2)
I’m not done organizing yet. There may be more to come…