In this blog post, we are dealing with a common yet interesting SQL problem: swapping column values in an entire table using a single update statement.
Salary table with the following structure:
id is the primary key for this table, and
sex is an ENUM value of type ('m', 'f'). Our goal is to write a SQL query that swaps all 'f' and 'm' values, i.e., changes all 'f' values to 'm' and vice versa, with a single update statement and no intermediate temporary tables.
For example, consider the following input:
The output would be:
In this section, we will compare three different solutions for this problem. Each solution differs in its approach, and we will discuss their main differences, strengths, and weaknesses.
The first solution uses the SQL
CASE statement to determine whether to set the
sex column to 'm' or 'f'.
UPDATE Salary SET sex = CASE WHEN sex = 'f' THEN 'm' WHEN sex = 'm' THEN 'f' END
The CASE statement evaluates each condition (in the WHEN clause) from top to bottom and sets the
sex value according to the first true condition. This solution is very straightforward and easy to read, but it requires evaluating two conditions, which may result in slightly slower performance. In a LeetCode test, this solution had a runtime of 482ms, beating 61.31% of all submissions.
The second solution takes advantage of the SQL
IIF statement, which is a shorthand way of writing a CASE expression. It takes three arguments: the boolean expression to evaluate, the value to return if the expression is true, and the value to return if the expression is false.
UPDATE Salary SET sex = IIF(sex = 'f', 'm', 'f')
This solution is even more concise than the first one. However, the
IIF function is specific to MSSQL and might not be supported in other SQL variants. This solution had a runtime of 480ms, slightly faster than the first one, and beat 61.95% of all submissions.
The third solution also uses a
CASE statement but in a different form. It directly compares the
sex value with 'm' and, if true, changes it to 'f'; otherwise, it changes it to 'm'.
UPDATE Salary SET sex = CASE sex WHEN 'm' THEN 'f' ELSE 'm' END
This solution is as readable as the first one but slightly more efficient because it evaluates only one condition. However, it is slower in performance, with a runtime of 686ms, beating only 12.56% of all submissions.
All three solutions meet the problem requirements. Solution 2 stands out for its brevity and slightly better performance in LeetCode tests. However, its performance advantage is negligible, and the difference might not be noticeable in a real-world database environment.
The solutions' ranking based on the LeetCode test performance is as follows:
- Solution 2 (IIF Statement)
- Solution 1 (CASE Statement)
- Solution 3 (Simplified CASE Statement)
Keep in mind that LeetCode is a controlled environment, and the performance of these queries might vary in different RDBMS and under different workloads.
You can find the original problem at LeetCode.
For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.