LEETCODE 627 - Swap Salary - Solution in MySQL | LEETCODE 627 Database SQL Solution

 LEETCODE | 627
Swap Salary

LEETCODE 627 - Swap Salary - Solution in MySQL | LEETCODE 627 Database SQL Solution :

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

 

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 of LEETCODE 627 - Swap Salary in MySQL:

update salary

set sex = IF(sex = 'm', 'f', 'm');

or,

 UPDATE salary

SET

sex = CASE sex

        WHEN 'm' THEN 'f'

        ELSE 'm'

    END;

To dynamically set a value to a column, we can use UPDATE statement together when CASE...WHEN... flow control statement.

Next Problem:

LEETCODE 196 - Delete Duplicate Emails - Solution in MySQL | LEETCODE 196 Database SQL Solution

Previous Problem: 

LEETCODE 1873 - Calculate Special Bonus - Solution in MySQL | LEETCODE 1873 Database SQL Solution

Ashraf LEETCODE Solutions