Earlier this week, we discussed a puzzle on how to swap values of a column without using the CASE statement. We received an overwhelming response from the community, with numerous creative and interesting solutions. In this blog post, we will highlight five of the most intriguing solutions and explore the concepts behind them.

The Problem

Let’s start by revisiting the original problem statement:

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

The goal is to swap the values in the “Gender” column. We will now explore five different solutions that were proposed by members of the community.

Solution 1: Using JOIN

Roji P Thomas proposed a simple and elegant solution using the UPDATE statement with a JOIN:

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

This solution leverages the power of JOIN to swap the values efficiently.

Solution 2: Using REPLACE

Aneel came up with an interesting solution that involves creating a temporary table and using the REPLACE function:

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

This solution combines both values and replaces the original value, resulting in a swap.

Solution 3: Using DIFFERENCE and SOUNDEX

Sijin Kumar V P amazed us with a solution that utilizes the DIFFERENCE and SOUNDEX functions:

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

By manipulating the values using these functions, Sijin was able to achieve the desired swap.

Solution 4: Using CROSS APPLY

Nikhildas presented a solution that employs the CROSS APPLY operator:

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

This solution utilizes CROSS APPLY to find the distinct gender value that is different from the current row’s gender, resulting in a swap.

Solution 5: Using VALUES

Mistermagooo provided a beautifully written solution using the VALUES clause:

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

Although this solution may be slower, the syntax used here is elegant and showcases the power of the VALUES clause.

These five solutions demonstrate the creativity and diverse approaches of the community. We encourage you to explore each solution and understand the underlying concepts.

Bonus Solution: A Comprehensive Blog Post

Madhivanan, a SQL Server MVP, went above and beyond by writing a complete blog post on this subject. We highly recommend reading his detailed analysis and solution.

Now, we have a challenge for you:

Challenge

1) Pick your best solution from the ones listed above and share it in the comments. This exercise will help us learn from each other’s perspectives.

2) Write your own solution that has not been covered in the 50+ solutions mentioned. We believe that there is no end to creativity, and we look forward to seeing your unique approach.

Thank you to everyone who participated in this puzzle. We are amazed by the creativity and knowledge of the SQL Server community. Stay tuned for future blog posts where we will explore more interesting SQL Server concepts.