sql server change bit value to opposite
- User
sql server change bit value to opposite
- Assistant
In SQL Server, if you want to change a
BITvalue to its opposite (i.e., toggle between0and1), you can use the bitwise NOT operator (~) or a simpleCASEexpression.Here are several ways to do it:
✅ Method 1: Using Bitwise NOT (
~)The
BITfield can be toggled using the~operator:UPDATE YourTable SET YourBitColumn = ~YourBitColumn WHERE SomeCondition;⚠️ Note: The
~operator works correctly only if the value is strictly0or1. If the column allowsNULL, be cautious —~NULLis stillNULL.
✅ Method 2: Using CASE Expression
This is more readable and avoids confusion:
UPDATE YourTable SET YourBitColumn = CASE WHEN YourBitColumn = 1 THEN 0 ELSE 1 END WHERE SomeCondition;This safely toggles the value regardless of whether it's
NULLor not (if you handleNULL, you can useISNULL()orCOALESCE()).
✅ Method 3: Using IIF (SQL Server 2012+)
If you're using SQL Server 2012 or newer:
UPDATE YourTable SET YourBitColumn = IIF(YourBitColumn = 1, 0, 1) WHERE SomeCondition;
🧪 Example Table and Usage:
CREATE TABLE Test ( ID INT PRIMARY KEY, IsActive BIT ); INSERT INTO Test (ID, IsActive) VALUES (1, 1), (2, 0); -- Toggle the IsActive bit UPDATE Test SET IsActive = ~IsActive WHERE ID IN (1, 2); -- Check result SELECT * FROM Test;
📝 Summary
Method Syntax Notes Bitwise NOT ~YourBitColumnFast, but less intuitive CASE Expression CASE WHEN =1 THEN 0 ELSE 1 ENDClear and safe IIF Function IIF(=1, 0, 1)Clean syntax (SQL Server 2012+) Let me know if you want to toggle a bit in a
SELECTstatement or in a variable!
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.