SQL: How to toggle a bit field
Here is a very simple yet nifty little function to perform a bit-wise operation in an SQL update statement. The following statement is an example that toggles a bit value for a field named IsLockedOut.
Update aspnet_Membership
Set
IsLockedOut = IsLockedOut ^ 1
Where
UserID=@UserID
6521 views
Comments
1-1 of 1
percyF
1/18/2010 12:27:37 AM
The quickest way I've found is to subtract the bit value from 1. Like this.
"UPDATE myTable set myToggleBit = 1 - myToggleBit Where ..."
It works because these bit fields are stored in Sql Server as 0 or 1.
1 - 0 (myTobbleBit) = 1
1 - 1 (myToggleBit) = 0
It's quicker, I suppose that doing an Exclusive Or (^) operation on the bit.
|