Reverse the data in a bit field 2

[Chopstik]

I recently was asked to create a new field in our database and did so without any major issue. It is a bit field so the data is either 0 or 1. However, I screwed up when I populated the data and reversed them so that 1 = FALSE and 0 = TRUE. What I'd like to do is reverse them. However, thinking through the problem, I'm not sure that I can easily accomplish this because, if I first set all of the 0 = 1, when I attempt to go back the other way it will set all of them back to 0. If I use a transaction, will that set my values as I'd expect them or is there another way to handle this? Any thoughts are appreciated. Thanks.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[bborissov]

[COLOR=blue]UPDATE[/color] YourTable
       [COLOR=blue]SET[/color] YourBitField = [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] YourBitField = 1
                               [COLOR=blue]THEN[/color] 0
                               [COLOR=blue]ELSE[/color] 1 [COLOR=blue]END[/color]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Chopstik]

It would be something that is seemingly so simple...
Thanks!

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[gmmastros]

AH. Bitwise Exclusive Or to the rescue.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color] (Id [COLOR=blue]Int[/color], BitField [COLOR=blue]Bit[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1,1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2,0)

[COLOR=blue]Select[/color] * [COLOR=blue]From[/color] @Temp

[COLOR=blue]Update[/color] @Temp [COLOR=blue]Set[/color] BitField = 1 ^ BitField

[COLOR=blue]Select[/color] * [COLOR=blue]From[/color] @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[earthandfire]

Not forgetting the bitwise NOT as in

update tablename
	set bitfield = ~ bitfield

 

[Chopstik]

At the risk of just handing stars out, all of the answers are good. I was unaware of the bitwise NOT but that is good to know. Thanks, all!

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill