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
CREATE TABLE SimpleTable (ID INT, Gender VARCHAR(10))
INSERT INTO SimpleTable (ID, Gender)
SELECT 1, 'female'
SELECT 2, 'male'
SELECT 3, 'male'
FROM SimpleTable
-- Insert Your Solutions here
-- Swap value of Column Gender
FROM SimpleTable
DROP TABLE SimpleTable

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

Solution by Roji P Thomas

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))
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

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)

About these ads

11 thoughts on “SQL SERVER – Solution of Puzzle – Swap Value of Column Without Case Statement

  1. 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…

  2. 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))

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

    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.

  4. 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’)


  5. Pingback: SQL SERVER – Simple Explanation and Puzzle with SOUNDEX Function and DIFFERENCE Function « SQL Server Journey with SQL Authority

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #033 | Journey to SQL Authority with Pinal Dave

  7. Hi Pinal,

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


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