# SQL SERVER – Solution of Puzzle – Swap Value of Column Without Case Statement

Earlier this week I asked a question where I asked how to Swap Values of the column without using CASE Statement. Read here: SQL SERVER – A Puzzle – Swap Value of Column Without Case Statement. I have proposed 3 different solutions in the blog posts itself. I had requested the help of the community to come up with alternate solutions and honestly I am stunned and amazed by the qualified entries. I will be not able to cover every single solution which is posted as a comment, however, I would like to for sure cover few interesting entries.

However, I am selecting 5 solutions which are different (not necessary they are most optimal or best – just different and interesting).

Just for clarity I am involving the original problem statement here.

```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
-- Swap value of Column Gender
SELECT *
FROM SimpleTable
GO
DROP TABLE SimpleTable
GO
```

Here are the five most interesting and different solutions I have received.

Solution by Roji P Thomas

```UPDATE S
SET S.Gender = D.Gender
FROM SimpleTable S
INNER JOIN SimpleTable D
ON S.Gender != D.Gender
```

I really loved the solutions as it is very simple and drives the point home – elegant and will work pretty much for any values (not necessarily restricted by the option in original question ‘male’ or ‘female’).

Solution by Aneel

```CREATE TABLE #temp(id INT, datacolumn CHAR(4))
INSERT INTO #temp
DECLARE @value1 CHAR(4), @value2 CHAR(4)
SET @value2 = 'gent'
UPDATE #temp
SET datacolumn = REPLACE(@value1 + @value2,datacolumn,'')
```

Aneel has very interesting solution where he combined both the values and replace the original value. I personally liked this creativity of the solution.

Solution by SIJIN KUMAR V P

```UPDATE SimpleTable
SET Gender = RIGHT(('fe'+Gender), DIFFERENCE((Gender),SOUNDEX(Gender))*2)
```

Sijin has amazed me with Difference and Soundex function. I have never visualized that above two functions can resolve the problem. Hats off to you Sijin.

Solution by Nikhildas

```UPDATE St
SET St.Gender = t.Gender
FROM SimpleTable St
CROSS Apply (SELECT DISTINCT gender FROM SimpleTable
WHERE St.Gender != Gender) t
```

` `

I was expecting that someone will come up with this solution where they use CROSS APPLY. This is indeed very neat and for sure interesting exercise. If you do not know how CROSS APPLY works this is the time to learn.

Solution by mistermagooo

```UPDATE SimpleTable
SET Gender=X.NewGender
FROM (VALUES('male','female'),('female','male')) AS X(OldGender,NewGender)
WHERE SimpleTable.Gender=X.OldGender
```

As per author this is a slow solution but I love how syntaxes are placed and used here. I love how he used syntax here. I will say this is the most beautifully written solution (not necessarily it is best).

Somehow I was confident Madhi – SQL Server MVP will come up with something which I will be compelled to read. He has written a complete blog post on this subject and I encourage all of you to go ahead and read it.

Now personally I wanted to list every single comment here. There are some so good that I am just amazed with the creativity. I will write a part of this blog post in future. However, here is the challenge for you.

Challenge: Go over 50+ various solutions listed to the simple problem here. Here are my two asks for you.

1) Pick your best solution and list here in the comment. This exercise will for sure teach us one or two things.

2) Write your own solution which is yet not covered already listed 50 solutions. I am confident that there is no end to creativity.

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

## SQL SERVER – Script Level Upgrade for Database ‘master’ Failed Because Upgrade Step ‘SSIS_hotfix_install.sql’ Encountered Error 15151

• sathyanarayanansrinivasan
June 14, 2012 7:51 am

I have never used that soundex function, as you said that was an excellent way of thinking I have never thought so Pinal.. Superb Sijin…

• Roji P Thomas
June 14, 2012 8:13 am

To be clear, I will NOT use any of my solutions if I have to really use it in Production code. I will either use the good old CASE or IIF.

And the solutions I like from the comments are

UPDATE SimpleTable
SET Gender = COALESCE(NULLIF(‘female’,Gender), ‘male’)

UPDATE SimpleTable
SET Gender = RIGHT(‘female’, 10 – LEN(Gender))

• Roji P Thomas,

Great comment as usual. However, I must acknowledge that your comments are indeed the first one to start the great conversation.

• harshcontractor
June 15, 2012 9:45 pm

Thanks Raji P Thomas for selecting my solution .

I have another simple solution.

UPDATE SimpleTable
SET Gender = LTRIM(REPLACE(‘ male female’,’ ‘ +Gender,”))
GO

• SIJIN KUMAR V P
June 14, 2012 4:47 pm

Thank you all.
I like the Solution by Roji P Thomas
Its a general one and works for any values.

UPDATE S
SET S.Gender = D.Gender
FROM SimpleTable S
INNER JOIN SimpleTable D
ON S.Gender != D.Gender

Once again thank you so much Pinal for the excellent Puzzle that added a lot to my knowledge.

• update SimpleTable
set Gender = coalesce(nullif(‘male’, Gender), nullif(‘female’, Gender))

• Hai Pinal,

Really i inspired by myself bcoz of one solution among the five is mine.

I personally like the solution by SIJIN KUMAR V P

By using COALESCE() and NULLIF()

UPDATE SimpleTable
SET Gender = COALESCE(NULLIF(‘female’,Gender), ‘male’)

Thanks,
Nikhildas
Cochin

• Thank sir,

• Hi Pinal,

Can we achieve something similar in linq? BTW i just enjoyed your session in GIDS’ Bangalore… Hope you will remember me ;)

Regards,
Wali

• Rahul Rathorerahul rathore
June 20, 2014 11:48 am

i did it this way
Create Table #temp(id int,gender char(2))
insert into #Temp values(1,’m’)
insert into #Temp values(2,’m’)
insert into #Temp values(3,’f’)

select * from #Temp

update t1 set t1.gender=(case when t1.gender=’m’ then ‘f’ else ‘m’ end ) from #temp t1