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
-- 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 (https://blog.sqlauthority.com)

SQL Function, SQL Scripts
Previous Post
SQL SERVER – A Quick Look at Logging and Ideas around Logging
Next Post
SQL SERVER – Free eBook Download – EPUB, MOBI, PDF Format

Related Posts

11 Comments. Leave new

  • 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…

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

    Reply
    • Roji P Thomas,

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

      Reply
    • 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

      Reply
  • 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.

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

    Reply
  • 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

    Reply
  • Thank sir,

    Reply
  • 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

    Reply
  • 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

    Reply
  • Normally we store Gender as Bit not as strings, in that case
    we can simply use bit-wise not operator

    UPDATE SimpleTable
    SET Gender = ~Gender

    Reply

Leave a Reply