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
  • name consists of English letters
  • sex is either 'm' or 'f'
  • 1 ≤ salary ≤ 100000

Visualization

Tap to expand
Swap Sex of Employees INPUT: Salary Table id name sex sal 1 A m 2500 2 B f 1500 3 C m 5500 4 D m 500 = male (m) = female (f) Requirement: m --> f f --> m ALGORITHM STEPS 1 Use CASE Statement Conditional logic in UPDATE 2 Check if sex = 'm' WHEN 'm' THEN 'f' 3 ELSE handles 'f' ELSE 'm' END 4 Single UPDATE No temp table needed UPDATE Salary SET sex = CASE WHEN sex = 'm' THEN 'f' ELSE 'm' END ; FINAL RESULT id name sex sal 1 A f 2500 2 B m 1500 3 C f 5500 4 D f 500 OK - Swapped! All values swapped: m --> f f --> m Key Insight: The CASE statement allows conditional value assignment in a single UPDATE query. Since we only have two possible values ('m' and 'f'), we check for one and default to the other using ELSE. This avoids temporary tables, multiple queries, or intermediate storage - achieving O(n) time complexity. TutorialsPoint - Swap Sex of Employees | Optimal Solution

Related Problems

Asked in
Amazon 12 Microsoft 8 Apple 6
24.1K Views
Medium Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen