SQL SERVER – A Puzzle – Swap Value of Column Without Case Statement

June 8, 2012

SQL, SQL Interview Questions and Answers, SQL Puzzle, SQL Server, SQL Tips and Tricks

For the last few weeks, I have been doing Friday Puzzles and I am really loving it. Yesterday I received a very interesting question by Navneet Chaurasia on Facebook Page. He was asked this question in one of the interview questions for job. Please read the original thread for a complete idea of the conversation. I am presenting the same question here.

Puzzle

Let us assume there is a single column in the table called Gender. The challenge is to write a single update statement which will flip or swap the value in the column. For example if the value in the gender column is ‘male’ swap it with ‘female’ and if the value is ‘female’ swap it with ‘male’.

Here is the quick setup script for the puzzle.

USE tempdb GO CREATE TABLE SimpleTable (ID INT, Gender VARCHAR(10)) GO INSERT INTO SimpleTable (ID, Gender) SELECT 1, 'female' UNION ALL SELECT 2, 'male' UNION ALL SELECT 3, 'male' GO SELECT * FROM SimpleTable GO

The above query will return following result set.

SQL SERVER - A Puzzle - Swap Value of Column Without Case Statement genderswap1

The puzzle was to write a single update column which will generate following result set.

SQL SERVER - A Puzzle - Swap Value of Column Without Case Statement genderswap2

There are multiple answers to this simple puzzle. Let me show you three different ways. I am assuming that the column will have either value ‘male’ or ‘female’ only.

Method 1: Using CASE Statement

I believe this is going to be the most popular solution as we are all familiar with 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

I totally understand it is the not cleanest solution but it will for sure work in giving situation.

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

Method 3: Using IIF in SQL Server 2012

If you are using SQL Server 2012 you can use IIF and get the same effect as CASE statement.

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

You can read my article series on SQL Server 2012 various functions over here.

Let us clean up.

DROP TABLE SimpleTable GO

Question to you:

I came up with three simple tricks where there is a single UPDATE statement which swaps the values in the column. Do you know any other simple trick? If yes, please post here in the comments. I will pick two random winners from all the valid answers. Winners will get 1) Print Copy of SQL Server Interview Questions and Answers 2) Free Learning Code for Online Video Courses

I will announce the winners on coming Monday.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL SERVER – Tricks to Replace SELECT * with Column Names – SQL in Sixty Seconds #017 – Video

SQL SERVER – Service Broker and CAP_CPU_PERCENT – Limiting SQL Server Instances to CPU Usage

No results found.

78 Comments. Leave new

Sachin Kamble
June 8, 2012 11:49 am
update SimpleTable
set Gender=isnull( nullif(‘male’,a)+’ ‘, ‘female’)
Reply
Dattatrey Sindol (Datta)
June 8, 2012 11:50 am
Good one Pinal!
I like the 2nd approach among the three :)
Reply
Nikhildas
June 8, 2012 11:58 am
Using CHOOSE Function in SQL Server 2012
Update SimpleTable
Set Gender = Choose(Len(Gender),”,”,”,’Female’,”,’Male’)
Reply
Durga
June 8, 2012 12:34 pm
UPDATE g1
SET g1.Gender = g2.Gender
FROM Gender g1
INNER JOIN Gender g2 ON g2.Gender g1.Gender
Reply
vikas
June 8, 2012 12:48 pm
Hi Pinal Sir, Check this answer
update s1
set s1.gender=( Select top 1 gender from simpletable s2
where s2.genders1.gender)
from simpletable s1
Reply
Saikat Mitra
June 8, 2012 1:16 pm
UPDATE SimpleTable
SET Gender = RIGHT(‘fe’+Gender,10-LEN(Gender))
Reply
Aneel
June 8, 2012 1:19 pm
/*
Another Approach: Using a dummy intermediate table.
*/
create table #temp(id int, datacolumn char(4))
insert into #temp
values(1,’lady’),(2,’gent’),(3,’lady’)
select * from #temp
declare @value1 char(4), @value2 char(4)
create table #dummy(datacolumn char(4))
set @value1 = ‘lady’
set @value2 = ‘gent’
insert into #dummy
values(@value1)
update #temp
set datacolumn = coalesce((select @value2 from #dummy where datacolumn = #temp.datacolumn),@value1)
select * from #temp
Reply
swapnil
June 8, 2012 1:24 pm
Select fullname,gender into #tempval1 from tblAdminManager where gender =’Male ‘
Select fullname,gender into #tempval2 from tblAdminManager where gender =’Female ‘
update #tempval1 set gender = ‘Female ‘
update #tempval2 set gender = ‘Male ‘
Select * from #tempval1
union
Select * from #tempval2
Reply
Ramkoti
June 8, 2012 1:42 pm
Great solutions from Pinal & Roji P Thomas. Thank u..
Reply
Aneel
June 8, 2012 1:43 pm
/*
Another Approach: Modified version of my previous solution. We dont even need a dummy table
*/
create table #temp(id int, datacolumn char(4))
insert into #temp
values(1,’lady’),(2,’gent’),(3,’lady’)
select * from #temp
declare @value1 char(4), @value2 char(4)
set @value1 = ‘lady’
set @value2 = ‘gent’
/*
Note that in the below subquery #temp can be replaced with any table
*/
update #temp
set datacolumn = coalesce((select top 1 @value1 from #temp t where @value2 = #temp.datacolumn),@value2)
select * from #temp
Reply
Saikat Mitra
June 8, 2012 3:26 pm
USE tempdb
GO
CREATE TABLE SimpleTable (ID INT, Gender VARCHAR(10))
GO
INSERT INTO SimpleTable (ID, Gender)
SELECT 1, ‘Ladies’
UNION ALL
SELECT 2, ‘Gents’
UNION ALL
SELECT 3, ‘Gents’
GO
SELECT *
FROM SimpleTable
DECLARE @tot AS NVARCHAR(20)
SET @tot=’LadiesGents’
UPDATE SimpleTable
SET Gender = SUBSTRING(@tot,(CHARINDEX(Gender,@tot,1)% 7)*7,LEN(@tot)-LEN(Gender)+1)
SELECT *
FROM SimpleTable
Reply
Nikhildas
June 8, 2012 3:27 pm
Update St
Set St.Gender = t.Gender
From SimpleTable St
Cross Apply (Select Distinct gender From SimpleTable Where St.Gender Gender) t
Reply
- Nikhildas
  June 8, 2012 3:29 pm
  Update St
  Set St.Gender = t.Gender
  From SimpleTable St
  Cross Apply (Select distinct gender From SimpleTable
  Where St.Gender Gender) t
  Reply
Arun Singh
June 8, 2012 3:30 pm
Hi pinal
Here is the answer:-
UPDATE SimpleTable
SET Gender=CHOOSE (ID,’male’,’female’,’female’)
GO
SELECT * FROM SimpleTable
Reply
Luca Lombardi
June 8, 2012 3:57 pm
Hi Pinal
Here is my solution:
;with Swapped as (
select distinct
T2.ID,T1.Gender
from SimpleTable T1
cross join SimpleTable T2
where T1.Gender T2.Gender
)
update T
set T.Gender = S.Gender
from dbo.SimpleTable T
join Swapped S on S.ID = T.ID
Reply
Nikhildas
June 8, 2012 4:20 pm
Updation with MERGE Operator
MERGE SimpleTable t
USING (SELECT DISTINCT Gender FROM SimpleTable) s
ON s.Gender t.gender
WHEN MATCHED THEN
UPDATE SET Gender = s.Gender;
Reply
- DHall
  June 9, 2012 1:09 am
  I like the creative use of the new SQL2012 MERGE operator, but you left out the “!=” between s.Gender and t.Gender. Here’s a revised version with a couple of additional lines that make it work even if there are values other than the two we’re swapping:
  MERGE SimpleTable t
  USING (SELECT DISTINCT Gender FROM SimpleTable) s
  ON s.Gender != t.Gender
  — these next 2 lines are only necessary if we’re not sure that ‘male’ and ‘female’ are the only values in the table
  AND t.Gender IN ( ‘male’, ‘female’ )
  AND s.Gender IN ( ‘male’, ‘female’ )
  WHEN MATCHED THEN
  UPDATE SET Gender = s.Gender;
  Reply
madhivanan
June 8, 2012 5:16 pm
Here are other solutions
Reply
Brian Tkatch
June 8, 2012 7:28 pm
UPDATE
SimpleTable
SET
Gender = fe_male.Gender
FROM
SimpleTable,
(SELECT ‘male’ UNION ALL SELECT ‘female’) fe_male(Gender)
WHERE
fe_male.Gender SimpleTable.Gender
Reply
carlos_sfc
June 8, 2012 8:51 pm
UPDATE SimpleTable
SET gender=g.gender
FROM SimpleTable as s
INNER JOIN(SELECT DISTINCT(gender) as gender FROM SimpleTable) as g
ON s.gender != g.gender
Reply
DHall
June 9, 2012 12:46 am
I like many of the other submissions better than my own, but since I went to the trouble of solving the puzzle, here are my two solutions:
USE tempdb
GO
CREATE TABLE SimpleTable (ID INT, Gender VARCHAR(10))
GO
INSERT INTO SimpleTable (ID, Gender)
SELECT 1, ‘female’
UNION ALL
SELECT 2, ‘male’
UNION ALL
SELECT 3, ‘male’
— I added two rows to test that my solutions work even if the Gender column contains other values than the two we’re swapping, but these are not necessary for the solution to work
UNION ALL
SELECT 4, ‘?’
UNION ALL
SELECT 5, NULL
GO
SELECT ‘Original’ AS Test, * FROM SimpleTable ORDER BY ID
;WITH Males AS ( SELECT ID FROM SimpleTable WHERE Gender = ‘male’)
,Females AS ( SELECT ID FROM SimpleTable WHERE Gender = ‘female’)
,Swapped AS (
SELECT ID, ‘male’ AS Gender FROM Females
UNION
SELECT ID, ‘female’ AS Gender FROM Males
)
UPDATE SimpleTable
SET Gender = Swapped.Gender
FROM Swapped
WHERE SimpleTable.ID = Swapped.ID
SELECT ‘Swapped’ AS Test, * FROM SimpleTable ORDER BY ID
— now put them back with a different algorithm
UPDATE SimpleTable SET Gender = ISNULL(NULLIF(ISNULL(NULLIF(ISNULL(NULLIF(Gender, ‘male’), ‘?’), ‘female’), ‘male’), ‘?’), ‘female’)
WHERE Gender IN ( ‘male’, ‘female’ ) — this is only necessary if we’re not sure that ‘male’ and ‘female’ are the only values in the table
SELECT ‘Restored’ AS Test, * FROM SimpleTable ORDER BY ID
DROP TABLE SimpleTable
Reply
Satheesh
June 9, 2012 2:46 am
UPDATE #SimpleTable
SET Gender=CASE WHEN LEN(Gender)=6 THEN Right(Gender,4) ELSE ‘fe’+Gender END
GO
Reply