Swap Salary
Problem
Salary(id, name, sex, salary). UPDATE to swap 'm' and 'f' in the sex column.
Input
s=[(1,'A','m',2500),(2,'B','f',1500),(3,'C','m',5500)]Output
[(1,'A','f',2500),(2,'B','m',1500),(3,'C','f',5500)]Every 'm' becomes 'f' and vice versa.
UPDATE Salary
SET sex = CASE WHEN sex = 'm' THEN 'f' ELSE 'm' END;Explanation
The goal is to flip every 'm' to 'f' and every 'f' to 'm' in one shot, without two separate updates (which would just flip everything back).
A single UPDATE with a CASE expression does it cleanly. For each row, CASE WHEN sex = 'm' THEN 'f' ELSE 'm' END looks at the current value and computes its opposite.
Because SQL evaluates the new value from the old row value before writing, there's no risk of an 'm' turning into 'f' and then back into 'm' — every row is decided independently and exactly once.
Example: a row with sex = 'm' hits the THEN 'f' branch, while sex = 'f' falls to ELSE 'm', so the whole table's genders swap in place.