{x}
blog image

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

Solution Explanation for SQL Query to Swap 'm' and 'f' values in a table

This problem requires updating a table's column to swap the values 'm' and 'f'. We can efficiently achieve this using a single UPDATE statement with a CASE expression or IF function in MySQL.

Approach 1: Using CASE Expression

This approach leverages the CASE expression to conditionally update the sex column. The CASE statement checks the current value of sex: If it's 'm', it's changed to 'f'; otherwise (implicitly meaning it's 'f'), it's changed to 'm'.

MySQL Code:

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

Explanation:

  • UPDATE salary: This specifies the table to be updated.
  • SET sex = CASE sex ... END: This sets the sex column's value based on the CASE expression.
  • WHEN 'm' THEN 'f': If the current sex is 'm', it's updated to 'f'.
  • ELSE 'm': If the current sex is not 'm' (i.e., 'f'), it's updated to 'm'.

Approach 2: Using IF Function

This approach utilizes the IF function, a more concise alternative to CASE for this specific scenario. IF(condition, value_if_true, value_if_false) evaluates the condition and returns the appropriate value.

MySQL Code:

UPDATE Salary
SET sex = IF(sex = 'f', 'm', 'f');

Explanation:

  • UPDATE Salary: Specifies the table to update.
  • SET sex = IF(sex = 'f', 'm', 'f'): This sets the sex column.
    • IF(sex = 'f', 'm', 'f'): Checks if sex is 'f'. If true, it sets sex to 'm'; otherwise, it sets it to 'f'.

Time Complexity Analysis

Both approaches have a time complexity of O(N), where N is the number of rows in the Salary table. The database needs to iterate through each row to apply the update. The exact time will depend on the database engine's optimization and the size of the table, but it's linear with respect to the number of rows.

Space Complexity Analysis

The space complexity is O(1) for both approaches. They use a constant amount of extra space regardless of the table size, as they only need to store a few variables for the update operation. No additional data structures proportional to the table's size are created.