How can you insert multiple rows into inventory and update the quantity by adding new values if item_id conflicts?
hard📝 Application Q9 of 15
PostgreSQL - Set Operations and Advanced Queries
How can you insert multiple rows into inventory and update the quantity by adding new values if item_id conflicts?
AINSERT INTO inventory(item_id, quantity) VALUES (1, 5), (2, 10) ON CONFLICT (item_id) DO UPDATE SET quantity = EXCLUDED.quantity * 2;
BINSERT INTO inventory(item_id, quantity) VALUES (1, 5), (2, 10) ON CONFLICT DO NOTHING;
CINSERT INTO inventory(item_id, quantity) VALUES (1, 5), (2, 10) ON CONFLICT (quantity) DO UPDATE SET quantity = EXCLUDED.quantity;
DINSERT INTO inventory(item_id, quantity) VALUES (1, 5), (2, 10) ON CONFLICT (item_id) DO UPDATE SET quantity = inventory.quantity + EXCLUDED.quantity;
Step-by-Step Solution
Solution:
Step 1: Understand multi-row insert with conflict handling
We want to add new quantities to existing ones on conflict by item_id.
Step 2: Check the update expression
INSERT INTO inventory(item_id, quantity) VALUES (1, 5), (2, 10) ON CONFLICT (item_id) DO UPDATE SET quantity = inventory.quantity + EXCLUDED.quantity; correctly adds existing quantity to new quantity using inventory.quantity + EXCLUDED.quantity.
Final Answer:
INSERT INTO inventory(item_id, quantity) VALUES (1, 5), (2, 10) ON CONFLICT (item_id) DO UPDATE SET quantity = inventory.quantity + EXCLUDED.quantity; -> Option D
Quick Check:
Use ON CONFLICT with DO UPDATE to add quantities [OK]
Quick Trick:Add quantities on conflict using existing + EXCLUDED values [OK]
Common Mistakes:
Using wrong conflict target column
Ignoring existing quantity in update
Using DO NOTHING which skips update
Master "Set Operations and Advanced Queries" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
