Swap Sex of Employees - Problem
You have a table Salary containing employee information including their sex (either 'f' for female or 'm' for male).
Task: Write a single UPDATE statement to swap all 'f' and 'm' values in the sex column. This means:
- Change all 'f' values to 'm'
- Change all 'm' values to 'f'
Important constraints:
- You must use a single UPDATE statement
- No intermediate temporary tables allowed
- Do not write any SELECT statements
Table Schema
Salary
| Column Name | Type | Description |
|---|---|---|
id
PK
|
int | Primary key - unique employee identifier |
name
|
varchar | Employee name |
sex
|
ENUM('m','f') | Employee sex - 'm' for male, 'f' for female |
salary
|
int | Employee salary amount |
Primary Key: id
Note: The sex column is an ENUM with only two possible values: 'm' and 'f'
Input & Output
Example 1 — Basic Sex Swap
Input Table:
| id | name | sex | salary |
|---|---|---|---|
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5000 |
Output:
| id | name | sex | salary |
|---|---|---|---|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5000 |
💡 Note:
The UPDATE statement swaps all sex values: employee A changes from 'f' to 'm', employee B changes from 'm' to 'f', and employee C changes from 'f' to 'm'. All other columns remain unchanged.
Example 2 — Single Employee
Input Table:
| id | name | sex | salary |
|---|---|---|---|
| 1 | John | m | 3000 |
Output:
| id | name | sex | salary |
|---|---|---|---|
| 1 | John | f | 3000 |
💡 Note:
Even with a single employee, the CASE WHEN statement works correctly, changing John's sex from 'm' to 'f' while preserving all other data.
Constraints
-
1 ≤ id ≤ 1000 -
nameconsists of English letters -
sexis either'm'or'f' -
1 ≤ salary ≤ 100000
Visualization
Tap to expand
Understanding the Visualization
1
Input
Original table with mixed 'f' and 'm' values
2
UPDATE
CASE WHEN swaps each value
3
Output
All sex values are swapped
Key Takeaway
🎯 Key Insight: Use CASE WHEN in UPDATE statements for conditional value swapping without temporary tables
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code