SQL interview question: Upsert With INSERT ... ON CONFLICT
Upsert the Stock Count
The nightly warehouse feed reports Widget B (product_id 2) at 450 units. The feed can't know whether a product already has an inventory row - your statement must handle both cases.
Write a single INSERT ... ON CONFLICT statement: insert product_id 2, product_name 'Widget B', current_stock 450, avg_daily_sales 40.0, lead_time_days 10, safety_stock 100 - but if the product already exists, update only its current_stock to the new value.
Note: Your change is verified automatically and rolled back afterwards.
This challenge changes data. Your statement runs on a private copy of the database, then we check the resulting table.
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 |