Simple way to Swap the value of a Bit field in SQL Server

A developer by mistake had set the Bit fields to the wrong value for a bunch of rows. So instead of setting them to ‘1’, he had set it to ‘0’. Here’s a simple way to fix the error by swapping the Bit field values:

We will use the Products table of the Northwind database for our example. The Products table has a Discontinued Column which has the datatype ‘Bit’. We will swap the Bit field values of all Products with CategoryID=2

Let us first see the Original values

SELECT ProductID, ProductName, Discontinued from Products
WHERE CategoryID=2

Now let us swap the Bit values using the Bitwise Exclusive OR (^) operator

UPDATE Products
SET Discontinued = Discontinued ^ 1
WHERE CategoryID = 2

Now after running the same select query, you will get the following output

Observe that all the Bit Values have been swapped using the ^ operator