Toggle a Bit with SQL
By Pete Freitag
I am working on a management dashboard for a client, which uses some AJAX here and there. One simple but really nice feature is that you can enabled or disable things just by clicking on an icon, the state is updated in the background using AJAX (I also add some animation to let the user know that it has been updated).
I wanted to be able to toggle the state of the bit
field without a care of the current state. Here's how you can do that in SQL:
UPDATE companies SET active_ind = active_ind ^ 1 WHERE id = ?
I'm using a ^
Bitwise (Exclusive OR or XOR) operator here to do this. To understand what's going on here let's look at a truth table for XOR:
p | q | p ^ q |
---|---|---|
1 | 1 | 0 |
1 | 0 | 1 |
0 | 1 | 1 |
0 | 0 | 0 |
Here's another method to toggle a bit value using SQL:
UPDATE companies SET active_ind = 1 - active_ind WHERE id = ?
This option works to toggle active_ind because:
1 - 0 = 1 1 - 1 = 0
I like this second option because it is a bit more readable than the first, I don't know if there is a performance difference between the two options, if so it is probably negligible in most applications.
Toggle a Bit with SQL was first published on January 04, 2007.
Discuss / Follow me on Twitter ↯
Tweet Follow @pfreitagComments
UPDATE companies
SET active_ind = ~active_ind
WHERE id = ?
I'll be using this from now on.
I have a similar problem:
I have been banging my head with this one.
Problem:
I have a SQL Query that sets a bit in a field (I didn't write the query)
Code:
UPDATE Table_name SET Options=Options|16 WHERE field='value'
And Also
Code:
UPDATE table_name SET Options=Options|8|4|64|128 WHERE field='value'
I was told this is setting the bit flags in the field and this query does work correctly.
My Question is:
How do I reverse the bit settings? I was told to use the ampersand "&" but it didn't work as I had thought.
So if I set options|16 I can unset options|16
Thanks in advance
--------------------------
so I found the the Tilda "~" (without the quotes) is the reverse binary option, but it didn't do as I thought it would :-(
SET active_ind = 1 - active_ind
?