SQL interview question: Transfer in a Transaction
The Atomic Stock Transfer
Warehouse rebalancing: 50 units of Widget A (product_id 1) move to the Widget C bin (product_id 3). If only one side of the move were saved, inventory counts would be wrong forever - both updates must succeed or neither.
Write a transaction: BEGIN, subtract 50 from Widget A's current_stock, add 50 to Widget C's, then COMMIT.
Note: This challenge accepts multiple statements separated by semicolons. If you forget COMMIT, your changes are discarded - just like a real connection closing. Runs on a scratch copy and is discarded afterwards.
Tables
product_inventory 4 rows
- product_id integer PK
- product_name text
- current_stock integer
- avg_daily_sales real
- lead_time_days integer
- safety_stock integer
| product_id | product_name | current_stock | avg_daily_sales | lead_time_days | safety_stock |
|---|---|---|---|---|---|
| 1 | Widget A | 500 | 25.0 | 7 | 50 |
| 2 | Widget B | 300 | 40.0 | 10 | 100 |
| 3 | Widget C | 200 | 15.0 | 5 | 30 |