LeetCode MySQL-627. Swap Salary

    AllenLi
    LeetCode MySQL

    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.
    The query result format is in the following example.

    寫一個 SQL Query來所有的 'f' 改為 'm' 字元，且反之亦然。注意使用獨立語法，不需建立臨時表格。

    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.

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

    Solution:
    1. 更新 Salary 的 table
    2. 設定「CASE WHEN」判定當 sex = ‘f’，則改為’m’，不是’f’的改為’f’。
    3. 顯示 Salary 的 table

    Code:

    UPDATE Salary 
SET sex = (CASE WHEN sex = 'f' THEN 'm' ELSE 'f' END);
SELECT * FROM Salary;

    LeetCode MySQL-627. Swap Salary

    Related Posts