Setting a bit - Help - Part II

[Phill Pafford]

Hello all,

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)


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

[chazzy]

& is not exactly "opposite." it works like any bitwise operator would work though.

[Phill Pafford]

Thanks for the help Chazzy.

I spent a day figuring this out (my head hurts), so I will post my findings if anyone needs them.

Sites to visit:

http://www.functionx.com/sql/Lesson02c.htm

http://www.functionx.com/references/numsystem.htm

http://www.helpwithpcs.com/courses/adding-subtracting-binary-numbers.htm

the easiest way for me to solve my problem was to reset the bit field back to zero and then reset to the correct value.

so basically run 2 queries.

The Problem:

I have 2 queries, but sometime I need to revert back to just having the first query ran and not the second. I thought I could just subtract the value added but its in binary format (head hurts again).

1 Query:

UPDATE table_name SET Options=Options|8|4|64|128 WHERE field='value'


2 Query:

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



so I found the the Tilda "~" (without the quotes) is the reverse binary option, but it didn't do as I thought it would :-(

But if I used it to reverse ALL the binary bits I have set and then added 1, I get zero again. Yeah!

so then I just need to re-run the 1st Query and I am set.

Set to Zero Query:

UPDATE table_name SET Options=((~Options|8|4|64|128|16) + 1) WHERE field='value'


I have added both bit options together in the same order ( I don't know if that matters or not) to zero out.

Hope this helps someone

[Phill Pafford]

Sorry all I thought I had figured it out, but I guess not

See previous thread

http://www.webdeveloper.com/forum/showthread.php?t=165413

Still need to know how to set or unset a bit/binary setting