Swap Salary - Video Solutions

Problem Overview: The Swap Salary problem gives you a table where the sex column contains values 'm' and 'f'. The task is to swap these values for every row without using a temporary table. After the update, every 'm' becomes 'f' and every 'f' becomes 'm'.

Approach 1: Single UPDATE with CASE Statement (O(n) time, O(1) space)

The most common solution uses a single UPDATE query combined with a CASE expression. The query scans each row and conditionally replaces the value of sex. If the value is 'm', it updates it to 'f'; otherwise it updates it to 'm'. The key insight is that SQL evaluates the CASE expression for each row during the update, so both swaps happen in one pass through the table. This approach is clean, readable, and works in any standard SQL environment.

Approach 2: Single UPDATE with In-place Logic (O(n) time, O(1) space)

This method uses character arithmetic to compute the swapped value directly. Since the ASCII codes of 'm' and 'f' are known, you can derive the opposite value using an expression like CHAR(ASCII('m') + ASCII('f') - ASCII(sex)). The formula effectively flips the value between the two characters without explicit condition checks. The database still scans the table once and updates each row in place. This technique is compact but less readable, so it’s typically used as a clever trick rather than production-style SQL.

Approach 3: Conditional Mapping with CASE Expression (O(n) time, O(1) space)

A more explicit variant of the first solution maps values using a CASE block that clearly lists both transformations. The statement checks the existing value and assigns the opposite value accordingly. This version prioritizes clarity and maintainability, which is why it often appears in database interview problems. The database engine performs a full table scan and updates the column using the evaluated expression.

Recommended for interviews: The single UPDATE query with a CASE expression is what interviewers expect. It demonstrates solid understanding of SQL conditional logic and avoids unnecessary temporary tables. The arithmetic trick shows creativity, but the CASE solution is clearer and aligns with how production database updates are typically written.