[LeetCode] UPDATE : 627. Swap Salary

1. 문제

 

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.

 

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 temp table(s).

 

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:

 

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

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

(1, A) and (3, C) were changed from 'm' to 'f'.

(2, B) and (4, D) were changed from 'f' to 'm'.

---

2. 답

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

---

3. KEY POINT

 

• UPDATE 테이블명 SET 컬럼 = 값 : 해당 테이블 명의 해당 컬럼에 해당 값 또는 식으로 대입
• 값 입력란에, CASE 문을 써서 조건에 따라 다른 값들로 수정해줄 수 있음

---

 

leetcode.com/problems/swap-salary/

Swap Salary - LeetCode