Swap Value In A Table Column

Introduction:  In This article we will see how to interchange the values of  a column in a table. Sometimes in our development cycle we may have to face a situation where we might have to interchange the values of a table column. SQL has provided us a very useful query for this purpose.

Example:  Suppose you have a Product table as below. And you have to swap the active field values i.e. True must be False and False must be True.

      ProductID           ProductName                                                                            Active

1	Chai	True
2	Chai Special	True
3	Aniseed Syrup	True
4	Chef Anton's Cajun Seasoning	True
5	Chef Anton's Gumbo Mix	False
6	Grandma's Boysenberry Spread	True
7	Uncle Bob's Organic Dried Pears	True
8	Northwoods Cranberry Sauce	True
9	Mishi Kobe Niku	False
10	Ikura	True

Use query stated below to swap the values.

UPDATE Products SET Active =
CASE Active
WHEN 1 THEN 0
WHEN 0 THEN 1
END

Result:

ProductID       ProductName                                                                           Active

1	Chai	False
2	Chai Special	False
3	Aniseed Syrup	False
4	Chef Anton's Cajun Seasoning	False
5	Chef Anton's Gumbo Mix	True
6	Grandma's Boysenberry Spread	False
7	Uncle Bob's Organic Dried Pears	False
8	Northwoods Cranberry Sauce	False
9	Mishi Kobe Niku	True
10	Ikura	False