Intermediate LevelSQL & MySQL
How does the ANSI SQL MERGE statement perform atomic multi-row UPSERTs?
❓ Interview Prompt: Explain ANSI SQL `MERGE INTO`, `WHEN MATCHED THEN UPDATE`, `WHEN NOT MATCHED THEN INSERT`, and multi-row synchronization.
The ANSI SQL 'MERGE' statement executes conditional 'INSERT', 'UPDATE', or 'DELETE' operations in a single atomic statement based on matching target and source table keys:
Benefits:
Simplifies batch synchronization between staging tables and production target tables.
Code Implementation Example
sql
MERGE INTO target_products t
USING staging_products s
ON (t.sku = s.sku)
WHEN MATCHED THEN
UPDATE SET t.price = s.price, t.updated_at = NOW()
WHEN NOT MATCHED THEN
INSERT (sku, price, created_at) VALUES (s.sku, s.price, NOW());💡Synchronizing staging and target product tables with ANSI SQL MERGE.
🎯 Interview Key Takeaway
Use the MERGE statement for atomic, batch-oriented UPSERT operations when supported by your database engine.
#SQL#MERGE#UPSERT#Batch Processing#Intermediate
Back to all SQL & MySQL Questions→