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
-- Insert Your Solutions here
-- 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
VALUES(1,'gent'),(2,'lady'),(3,'lady')
DECLARE @value1 CHAR(4), @value2 CHAR(4)
SET @value1 = 'lady'
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).

Bonus: Solution by Madhivanan

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 (http://blog.SQLAuthority.com)