Fix Reversed Patch Panel Ports
A telecom engineer discovered that the inventory for an exchange was loaded with every port direction reversed: ports recorded as 'IN' are really outbound, and ports recorded as 'OUT' are really inbound. Ports marked 'BOTH' were recorded correctly.
Write a single UPDATE statement (no temporary tables, no second statement) that changes every 'IN' to 'OUT' and every 'OUT' to 'IN', leaving 'BOTH' unchanged.
Do not return anything. The judge checks the full contents of patch_ports afterwards.
Tables
patch_ports — One row per port on the exchange patch panels.
| Column | Type | Description |
|---|---|---|
port_id (key) | INTEGER | Unique port id |
panel_label | TEXT | Panel and position, such as 'PNL-03/17' |
direction | TEXT | Traffic direction: 'IN', 'OUT' or 'BOTH' |
Example
patch_ports
| port_id | panel_label | direction |
|---|---|---|
| 1 | PNL-01/01 | IN |
| 2 | PNL-01/02 | OUT |
| 3 | PNL-01/03 | BOTH |
| 4 | PNL-02/01 | IN |
| 5 | PNL-02/02 | IN |
| 6 | PNL-02/03 | OUT |
Expected output
| port_id | panel_label | direction |
|---|---|---|
| 1 | PNL-01/01 | OUT |
| 2 | PNL-01/02 | IN |
| 3 | PNL-01/03 | BOTH |
| 4 | PNL-02/01 | OUT |
| 5 | PNL-02/02 | OUT |
| 6 | PNL-02/03 | IN |
Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.
SQL Path · Changing Data · problem 60 of 70 · SQLite
The idea
A swap looks like it needs a temporary value, but SQL does not. Within a single UPDATE, every SET expression is evaluated against the row's original value, so CASE direction WHEN 'IN' THEN 'OUT' WHEN 'OUT' THEN 'IN' END flips both kinds of rows in one pass with no risk of a row being flipped twice.
The third value is the trap. A CASE with no matching branch returns NULL (which the NOT NULL column would reject), and a lazy ELSE 'IN' silently corrupts 'BOTH' ports. The solution restricts the update with WHERE direction IN ('IN', 'OUT'), which also avoids rewriting rows that do not change. The alternative updates every row but uses ELSE direction to keep other values as they are.
Splitting the job into two UPDATEs is the classic mistake: the second statement sees the result of the first and flips those rows back.
Solution
UPDATE patch_ports
SET direction = CASE direction
WHEN 'IN' THEN 'OUT'
WHEN 'OUT' THEN 'IN'
END
WHERE direction IN ('IN', 'OUT');Another way
UPDATE patch_ports
SET direction = CASE
WHEN direction = 'IN' THEN 'OUT'
WHEN direction = 'OUT' THEN 'IN'
ELSE direction
END;Why common answers fail
Two updates in a row: the second also flips the rows the first just changed, so every IN/OUT port ends up 'IN'.
UPDATE patch_ports SET direction = 'OUT' WHERE direction = 'IN';
UPDATE patch_ports SET direction = 'IN' WHERE direction = 'OUT';The ELSE branch also catches 'BOTH' and turns it into 'IN'.
UPDATE patch_ports SET direction = CASE WHEN direction = 'IN' THEN 'OUT' ELSE 'IN' END;What the interviewer is checking
Checks that you understand an UPDATE evaluates all rows against their old values, and that you protect values outside the swap.
Mistakes to avoid
- Running two UPDATE statements, which collapses both values into one.
- Using ELSE with a fixed value, which changes rows that should stay as they are.
- Omitting both WHERE and ELSE, so unmatched rows become NULL (or the statement fails on a NOT NULL column).
Follow-ups to expect
- How would you do it if you were forced to use two statements? (Use a placeholder value: IN to TMP, OUT to IN, TMP to OUT.)
- Swap the values of two columns in the same row (SET a = b, b = a works because both read the old row).
- Only fix ports on one panel (add AND panel_label LIKE 'PNL-03/%').
In real work
Data-fix scripts often reverse flags that were loaded backwards, such as debit/credit or inbound/outbound, and must do it atomically in one statement.