Toggle a Bit with SQL


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).

shows the transition of states

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

30 people found this page useful, what do you think?


Trackback Address: 615/89828BEA2888C0C5435AF00E3ED77DBD


On 01/04/2007 at 9:51:50 AM UTC Neil Middleton wrote:
Uh... 0 ^ 0 = 0

On 01/04/2007 at 10:07:28 AM UTC Rick O wrote:
Why not:

SET active_ind = 1 - active_ind


On 01/04/2007 at 10:26:06 AM UTC Pete Freitag wrote:
Oops thanks Neil, I was copying and pasting the rows of hmtl, and forgot to change the last one... Fixed.

On 01/04/2007 at 10:28:12 AM UTC Pete Freitag wrote:
Rick that's a good solution as well, and probably a bit more readable. Thanks!

On 01/04/2007 at 9:05:43 PM UTC Dale Fraser wrote:
SET active_ind = 1 - active_ind

Does that work?

1 - 0 = 0 0 - 1 = -1 does this end up as bit 1

On 01/05/2007 at 5:35:17 AM UTC Ben Nadel wrote:
Nice tip! I have never used bit-wise manipulation in SQL. It's something I should look into.

On 01/05/2007 at 7:39:01 AM UTC Pete Freitag wrote:
Dale, yes it does work:

1 - 0 = 1

1 - 1 = 0

On 01/05/2007 at 9:22:01 AM UTC Kris wrote:
if 1 is always 1 then you can use the NOT option to revse the value of active_ind.

UPDATE companies SET active_ind = ~active_ind WHERE id = ?

On 01/12/2007 at 11:34:36 AM UTC Steve wrote:
Very elegant. I have been using the ternary operator in PHP, but that requires passing in a variable from the middleware tier.

I'll be using this from now on.

On 11/09/2007 at 12:51:31 PM UTC Phill Pafford wrote:

I have a similar problem:

I have been banging my head with this one.


I have a SQL Query that sets a bit in a field (I didn't write the query)


UPDATE Table_name SET Options=Options|16 WHERE field='value'

And Also


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 :-(

On 10/24/2008 at 12:48:10 PM UTC MoMad wrote:
Try Options & ~16.

Basically, you want to invert 16, then AND it with your options: 16 = 0001 0000 ~ = 1110 1111

If Options is 217 = 1101 1001

SELECT Options & ~16 will give: 201 = 1100 1001

On 08/24/2010 at 12:42:47 PM UTC Stephen B Craver wrote:
Use ABS to get the absolute value (-1 becomes 1)...

DECLARE @Trend AS BIT SET @Trend = 0 SELECT @Trend, ABS(@Trend-1)

On 06/17/2012 at 10:22:12 AM UTC wrote:

since when it turned into 0-1


On 02/16/2013 at 8:15:31 AM UTC CodeTech wrote:
To toggle a bit in a bitfield in your table, try this: UPDATE user SET vis=IF(vis&4,vis&~4,vis|4) WHERE id=$authuserid LIMIT 1

The value "4" can be replaced with 2^bitposition, ie. 1,2,4,8,16,32,64...

Post a Comment


Spell Checker by Foundeo

Recent Entries


did you hack my cf?