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)
11 Comments. Leave new
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…
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.
Thanks Raji P Thomas for selecting my solution .
I have another simple solution.
UPDATE SimpleTable
SET Gender = LTRIM(REPLACE(‘ male female’,’ ‘ +Gender,”))
GO
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
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
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