# 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:

| Value | Meaning |
|---|---|
| `m` | male |
| `f` | female |

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

```sql
Salary
```

| Column | Type |
|---|---|
| id | int |
| name | varchar |
| sex | enum |
| salary | int |

`id` is the primary key.

The `sex` column has enum values `m` and `f`.

## Example

Input:

| id | name | sex | salary |
|---|---|---|---|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |

After the update:

| id | name | sex | salary |
|---|---|---|---|
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |

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:

| Current `sex` | New `sex` |
|---|---|
| `m` | `f` |
| `f` | `m` |

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:

```sql
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

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

## Explanation

The statement updates every row in the table.

```sql
UPDATE Salary
```

means we are modifying the `Salary` table.

```sql
SET sex = ...
```

means the update only changes the `sex` column.

The `CASE` expression decides the new value:

```sql
WHEN sex = 'm' THEN 'f'
```

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

```sql
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

| Metric | Value | Why |
|---|---|---|
| Time | `O(n)` | The database updates each row once |
| Space | `O(1)` | No temporary table is required |

Here, `n` is the number of rows in `Salary`.

## Alternative Solution: Use `IF` in MySQL

MySQL also supports the `IF` function.

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

This means:

```text
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:

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

Insert sample rows:

```sql
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:

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

Expected table:

| id | name | sex | salary |
|---|---|---|---|
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |

Single row case:

```sql
TRUNCATE TABLE Salary;

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

Expected after update:

| id | name | sex | salary |
|---|---|---|---|
| 1 | A | f | 2500 |