Swap Salary SQL Leetcode

Write an SQL query 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.

Table: Salary

+-------------+----------+

| Column Name | Type |

+-------------+----------+

| id | int |

| name | varchar |

| sex | ENUM |

| salary | int |

+-------------+----------+

id is the primary key for this table.

The sex column is ENUM value of type ('m', 'f').

The table contains information about an employee.

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

Swap Salary SQL Leetcode Solution

UPDATE salary

SET sex = CASE

WHEN sex = 'f' THEN 'm'

WHEN sex = 'm' THEN 'f'

ELSE sex

END

Explanation

The SQL query you provided will update the 'sex' column in the 'salary' table, by swapping the values 'm' and 'f' for all records where the current 'sex' value is either 'm' or 'f'. For any other 'sex' values, the query will leave them unchanged.

The query uses a conditional expression with the CASE statement to check the current value of the 'sex' column for each row. If the current value is 'f', it will change it to 'm'. If the current value is 'm', it will change it to 'f'. If the current value is not 'm' or 'f', the query will leave it unchanged.

Note that the effectiveness and ethics of updating gender data in this way should be carefully considered, as it may not accurately reflect individuals' gender identity or expression. It's important to handle gender data with sensitivity and respect for diversity.

Disclaimer: The above Problem is generated by Leetcode but the Solution is provided by ShouterFolk