[LeetCode][MySQL] 627. Swap Salary

Easy 🔗627. Swap Salary

📝문제 요약

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

✏️문제 풀이

UPDATE

UPDATE Salary

• Salary라는 테이블의 데이터를 수정(업데이트)

SET

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

• SET sex = ... : sex라는 컬럼의 값을 변경
• CASE WHEN sex = 'f' THEN 'm' ELSE 'f' END
  • sex 값이 'f'인 경우, 'm' 으로 변경
  • 그 외의 경우 (즉, 'm'인 경우), 'f'으로 변경

💯제출 코드

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