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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook