Swap Sex of Employees - Problem
You are tasked with updating employee records in a company database. The HR department needs to swap all gender values in the employee table as part of a data anonymization process.
Given a table Salary with the following structure:
| Column Name | Type |
|---|---|
| id | int |
| name | varchar |
| sex | ENUM('m', 'f') |
| salary | int |
Write a single UPDATE statement that swaps all 'f' values to 'm' and all 'm' values to 'f' in the sex column. You must:
- Use only one UPDATE statement
- No intermediate temporary tables
- No SELECT statements
- Transform all records simultaneously
This is a classic SQL puzzle that tests your understanding of conditional updates and SQL functions!
Input & Output
basic_swap.sql — Standard Case
$
Input:
Salary table:
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
+----+------+-----+--------+
›
Output:
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
+----+------+-----+--------+
💡 Note:
All 'm' values are changed to 'f' and all 'f' values are changed to 'm'. The id, name, and salary columns remain unchanged.
single_gender.sql — All Same Gender
$
Input:
Salary table:
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1 | John | m | 3000 |
| 2 | Mike | m | 4000 |
| 3 | Dave | m | 2000 |
+----+------+-----+--------+
›
Output:
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1 | John | f | 3000 |
| 2 | Mike | f | 4000 |
| 3 | Dave | f | 2000 |
+----+------+-----+--------+
💡 Note:
Even when all employees have the same gender, each 'm' is swapped to 'f'. The operation works regardless of the distribution of gender values.
empty_table.sql — Edge Case
$
Input:
Salary table:
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
(empty table)
›
Output:
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
(empty table)
💡 Note:
When the table is empty, the UPDATE statement executes successfully but affects 0 rows. No error occurs and the result is still an empty table.
Constraints
- The table contains only ENUM values 'm' and 'f' for the sex column
- Must use exactly one UPDATE statement
- No temporary tables or intermediate steps allowed
- No SELECT statements permitted
Visualization
Tap to expand
Understanding the Visualization
1
Identify Pattern
Recognize this is a binary swap problem - only two possible values that need to exchange positions
2
Choose Method
Use CASE statement to create conditional mapping in single UPDATE statement
3
Apply Atomically
Execute transformation on all rows simultaneously without intermediate states
Key Takeaway
🎯 Key Insight: Use conditional logic (CASE/IF) to create a mapping function that transforms each value to its opposite in a single atomic UPDATE operation
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code