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.

ID           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:

ID       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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s