Dot Net Collections: Swap Value of Column in SQL Server

Monday, 25 August 2014

Swap Value of Column in SQL Server/ write a single update statement to swap the value in the column/replace male to female and female to male in SQL Server.

There are multiple option to this simple problem in SQL Server.

Method 1: Using CASE Statement

UPDATE SimpleTable
SET Gender = CASE Gender WHEN 'male' THEN 'female' ELSE 'male' END
GO
SELECT *
FROM SimpleTable
GO

Method 2: Using REPLACE Function

UPDATE SimpleTable
SET Gender = REPLACE(('fe'+Gender),'fefe','')
GO
SELECT *
FROM SimpleTable

GO

Method 3: Using IIF (Only SQL Server 2012 or above)

UPDATE SimpleTable
SET Gender = IIF(Gender = 'male', 'female', 'male')
GO
SELECT *
FROM SimpleTable