LeetCode 627: Swap Salary

Problem Restatement

We are given a table named Salary.

Each row stores information about one employee.

The sex column contains only two possible values:

We need to update the table so that every m becomes f, and every f becomes m.

The problem requires one UPDATE statement.

We should not use a temporary table, and we should not write a SELECT statement.

Table

Salary

id is the primary key.

The sex column has enum values m and f.

Example

Input:

After the update:

Only the sex column changes.

The id, name, and salary columns stay the same.

First Thought: Update Based on Current Value

We need a conditional update.

For each row:

This is exactly what a SQL CASE expression is for.

A CASE expression lets us compute a new value depending on the old value.

Key Insight

The update should assign sex to the opposite value.

There are only two possible values, so the rule is simple:

CASE
    WHEN sex = 'm' THEN 'f'
    ELSE 'm'
END

The ELSE branch is safe because the problem says sex can only be m or f.

Algorithm

For every row in Salary:

1. Check the current value of sex.
2. If it is m, set it to f.
3. Otherwise, set it to m.

This is done directly inside one UPDATE statement.

SQL Solution

UPDATE Salary
SET sex = CASE
    WHEN sex = 'm' THEN 'f'
    ELSE 'm'
END;

Explanation

The statement updates every row in the table.

UPDATE Salary

means we are modifying the Salary table.

SET sex = ...

means the update only changes the sex column.

The CASE expression decides the new value:

WHEN sex = 'm' THEN 'f'

If the old value is m, the new value becomes f.

ELSE 'm'

If the old value is not m, it must be f, so the new value becomes m.

No other columns are assigned new values, so id, name, and salary remain unchanged.

Correctness

Consider any row in the Salary table.

If its original sex value is m, the WHEN sex = 'm' branch applies, and the query sets sex to f.

If its original sex value is f, the WHEN condition is false. Since the problem allows only m and f, the ELSE branch applies, and the query sets sex to m.

Therefore, every m is changed to f, and every f is changed to m.

The query modifies only the sex column, so all other employee information remains unchanged.

Complexity

Here, n is the number of rows in Salary.

Alternative Solution: Use IF in MySQL

MySQL also supports the IF function.

UPDATE Salary
SET sex = IF(sex = 'm', 'f', 'm');

This means:

if sex is m, use f; otherwise, use m

The CASE version is more portable across SQL dialects.

The IF version is shorter and common in MySQL.

Testing

Create the table:

CREATE TABLE Salary (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    sex ENUM('m', 'f'),
    salary INT
);

Insert sample rows:

INSERT INTO Salary (id, name, sex, salary) VALUES
(1, 'A', 'm', 2500),
(2, 'B', 'f', 1500),
(3, 'C', 'm', 5500),
(4, 'D', 'f', 500);

Run the solution:

UPDATE Salary
SET sex = CASE
    WHEN sex = 'm' THEN 'f'
    ELSE 'm'
END;

Expected table:

Single row case:

TRUNCATE TABLE Salary;

INSERT INTO Salary (id, name, sex, salary) VALUES
(1, 'A', 'm', 2500);

Expected after update: