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'.
This approach utilizes the SQL CASE statement within an UPDATE to switch values directly. The CASE statement allows you to perform conditional operations inside your SQL queries.
The solution employs a SQL update command that uses the CASE statement. It checks if the sex field equals 'm' and swaps to 'f' or defaults to 'm' otherwise.
Time Complexity: O(n), where n is the number of records to update as each row is processed once.
Space Complexity: O(1), as no extra space is used apart from variables maintained by the database.
This approach uses a conditional logic that directly swaps values without additional complexity, applicable in SQL statement logic.
This method uses an SQL IF construct to perform the conditional change. IF checks whether the sex value equals 'm' and switches to 'f', otherwise switches to 'm'.
Time Complexity: O(n), due to examining and updating each row in the table.
Space Complexity: O(1), as it only alters existing database entries without using additional space.
The idea for this approach is to use a SQL CASE statement to swap values in a single column update operation. By specifying conditions, you can change 'f' to 'm' and 'm' to 'f' directly.
In this SQL statement, we are using a CASE clause to determine the new value for the sex column. For each row, if the current value of sex is 'm', it is changed to 'f'. If the current value is 'f', it is changed to 'm'. This ensures that all values are swapped in a single pass and without utilizing any temporary tables.
Time Complexity: O(n), where n is the number of rows in the table, as it requires a single pass to update all records.
Space Complexity: O(1), as no additional space is required apart from the input itself.
| Approach | Complexity |
|---|---|
| Single Update with CASE Statement | Time Complexity: O(n), where n is the number of records to update as each row is processed once. |
| Single Update with In-place Logic | Time Complexity: O(n), due to examining and updating each row in the table. |
| Using SQL `CASE` Statement for Update | Time Complexity: O(n), where n is the number of rows in the table, as it requires a single pass to update all records. |
LeetCode 627: Swap Salary [SQL] • Frederik Müller • 5,152 views views
Watch 9 more video solutions →Practice Swap Salary with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor