Skip to main content
Talentd

Swap Salary - Solution & Explanation

EasyDatabase5 min readAsked at: Amazon, Microsoft, Meta +2
Practice this problem

Problem Statement

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'.

Approach Overview

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.

Approach 1: Single Update with CASE Statement

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.

Code

SQL

SQL

Complexity

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.

Try this approach in the editor →

Approach 2: Single Update with In-place Logic

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'.

Code

SQL

SQL

Complexity

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.

Try this approach in the editor →

Approach 3: Using SQL `CASE` Statement for Update

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.

Code

SQL

SQL

Complexity

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.

Try this approach in the editor →

Approach 4: Swap Sex Using a Single UPDATE Statement

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.

Code

MySQL

MySQL

Try this approach in the editor →

Complexity Comparison

ApproachComplexity
Single Update with CASE Statement

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.

Single Update with In-place Logic

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.

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.
Space Complexity: O(1), as no additional space is required apart from the input itself.

Swap Sex Using a Single UPDATE Statement—

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Single UPDATE with CASE StatementO(n)O(1)Best general solution. Clear, readable, and widely accepted in SQL interviews.
In-place ASCII LogicO(n)O(1)Useful as a clever trick when demonstrating SQL expression manipulation.
Explicit CASE MappingO(n)O(1)Preferred when clarity and maintainability of the SQL query are priorities.

Video Solution

LeetCode Interview SQL Question with Detailed Explanation | Practice SQL | LeetCode 627 • Everyday Data Science • 14,664 views views

Watch 9 more video solutions →

Frequently Asked Questions

Is Swap Salary easy or hard?
Swap Salary is classified as an Easy SQL problem. It mainly tests familiarity with UPDATE queries and CASE expressions. The logic is straightforward once you realize the swap can be performed directly within a single SQL statement.
Swap Salary Python/Java solution
The problem is designed for SQL rather than general-purpose languages. Instead of writing loops in Python or Java, you execute a single SQL UPDATE statement that conditionally swaps the values in the column using CASE logic.
How to solve Swap Salary in O(n)?
Use a single UPDATE query with a CASE expression such as: UPDATE salary SET sex = CASE WHEN sex = 'm' THEN 'f' ELSE 'm' END. The database processes each row once and replaces the value based on the condition. This achieves O(n) time with constant extra space.
What is the best approach for Swap Salary?
The best approach uses a single SQL UPDATE statement with a CASE expression. The query checks the value of the sex column and swaps 'm' to 'f' and 'f' to 'm' in one pass. This runs in O(n) time with O(1) extra space because the database simply scans and updates each row.
Is Swap Salary asked at Google/Amazon/Meta?
Swap Salary is a common SQL screening problem used in database interview rounds. Variants of conditional updates and CASE expressions appear in interviews at companies that test SQL skills, including large tech companies and data-focused roles.
What data structure is used in Swap Salary?
The problem operates directly on a relational database table. No additional data structures like arrays or hash maps are required. The solution relies on SQL conditional logic, specifically the CASE expression within an UPDATE statement.
What is the time complexity of Swap Salary?
The time complexity is O(n), where n is the number of rows in the table. The database engine performs a single scan of the table while applying the UPDATE statement. No additional structures or temporary tables are required, so space complexity remains O(1).

Ready to solve this problem?

Practice Swap Salary with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary