Table: Salary
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| id | int |
| name | varchar |
| sex | ENUM |
| salary | int |
+-------------+----------+
id is the primary key (column with unique values) for this table.
The sex column is ENUM (category) value of type ('m', 'f').
The table contains information about an employee.
Write a solution to swap all 'f' and 'm' values (i.e., change all 'f' values to 'm' and vice versa) with a single update statement and no intermediate temporary tables.
Note that you must write a single update statement, do not write any select statement for this problem.
The result format is in the following example.
Example 1:
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 | +----+------+-----+--------+ Explanation: (1, A) and (3, C) were changed from 'm' to 'f'. (2, B) and (4, D) were changed from 'f' to 'm'.
Problem Overview: The Swap Salary problem gives you a table where the sex column contains values 'm' and 'f'. The task is to swap these values for every row without using a temporary table. After the update, every 'm' becomes 'f' and every 'f' becomes 'm'.
Approach 1: Single UPDATE with CASE Statement (O(n) time, O(1) space)
The most common solution uses a single UPDATE query combined with a CASE expression. The query scans each row and conditionally replaces the value of sex. If the value is 'm', it updates it to 'f'; otherwise it updates it to 'm'. The key insight is that SQL evaluates the CASE expression for each row during the update, so both swaps happen in one pass through the table. This approach is clean, readable, and works in any standard SQL environment.
Approach 2: Single UPDATE with In-place Logic (O(n) time, O(1) space)
This method uses character arithmetic to compute the swapped value directly. Since the ASCII codes of 'm' and 'f' are known, you can derive the opposite value using an expression like CHAR(ASCII('m') + ASCII('f') - ASCII(sex)). The formula effectively flips the value between the two characters without explicit condition checks. The database still scans the table once and updates each row in place. This technique is compact but less readable, so it’s typically used as a clever trick rather than production-style SQL.
Approach 3: Conditional Mapping with CASE Expression (O(n) time, O(1) space)
A more explicit variant of the first solution maps values using a CASE block that clearly lists both transformations. The statement checks the existing value and assigns the opposite value accordingly. This version prioritizes clarity and maintainability, which is why it often appears in database interview problems. The database engine performs a full table scan and updates the column using the evaluated expression.
Recommended for interviews: The single UPDATE query with a CASE expression is what interviewers expect. It demonstrates solid understanding of SQL conditional logic and avoids unnecessary temporary tables. The arithmetic trick shows creativity, but the CASE solution is clearer and aligns with how production database updates are typically written.
This approach utilizes the SQL CASE statement within an UPDATE to switch values directly. The CASE statement allows you to perform conditional operations inside your SQL queries.
The solution employs a SQL update command that uses the CASE statement. It checks if the sex field equals 'm' and swaps to 'f' or defaults to 'm' otherwise.
SQL
Time Complexity: O(n), where n is the number of records to update as each row is processed once.
Space Complexity: O(1), as no extra space is used apart from variables maintained by the database.
This approach uses a conditional logic that directly swaps values without additional complexity, applicable in SQL statement logic.
This method uses an SQL IF construct to perform the conditional change. IF checks whether the sex value equals 'm' and switches to 'f', otherwise switches to 'm'.
SQL
Time Complexity: O(n), due to examining and updating each row in the table.
Space Complexity: O(1), as it only alters existing database entries without using additional space.
The idea for this approach is to use a SQL CASE statement to swap values in a single column update operation. By specifying conditions, you can change 'f' to 'm' and 'm' to 'f' directly.
In this SQL statement, we are using a CASE clause to determine the new value for the sex column. For each row, if the current value of sex is 'm', it is changed to 'f'. If the current value is 'f', it is changed to 'm'. This ensures that all values are swapped in a single pass and without utilizing any temporary tables.
SQL
Time Complexity: O(n), where n is the number of rows in the table, as it requires a single pass to update all records.
Space Complexity: O(1), as no additional space is required apart from the input itself.
According to the problem requirements, we only need to use a single UPDATE statement to swap the sex of all employees. We can achieve this using conditional expressions in SQL.
MySQL
| Approach | Complexity |
|---|---|
| Single Update with CASE Statement | Time Complexity: O(n), where n is the number of records to update as each row is processed once. |
| Single Update with In-place Logic | Time Complexity: O(n), due to examining and updating each row in the table. |
| Using SQL `CASE` Statement for Update | Time Complexity: O(n), where n is the number of rows in the table, as it requires a single pass to update all records. |
| Swap Sex Using a Single UPDATE Statement | — |
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Single UPDATE with CASE Statement | O(n) | O(1) | Best general solution. Clear, readable, and widely accepted in SQL interviews. |
| In-place ASCII Logic | O(n) | O(1) | Useful as a clever trick when demonstrating SQL expression manipulation. |
| Explicit CASE Mapping | O(n) | O(1) | Preferred when clarity and maintainability of the SQL query are priorities. |
LeetCode Interview SQL Question with Detailed Explanation | Practice SQL | LeetCode 627 • Everyday Data Science • 13,876 views views
Watch 9 more video solutions →Practice Swap Salary with our built-in code editor and test cases.
Practice on FleetCode