Part 50: The ANSI SQL MERGE Statement

The standard ANSI SQL solution for synchronization between two tables (supported in Oracle, SQL Server, and PostgreSQL 15+).

1. Syntax

MERGE INTO inventory AS target
USING staging_inventory AS source
ON target.product_id = source.product_id
WHEN MATCHED THEN
  UPDATE SET target.quantity = source.quantity
WHEN NOT MATCHED THEN
  INSERT (product_id, quantity) VALUES (source.product_id, source.quantity);

Key Takeaways

• MERGE handles simultaneous inserts, updates, and deletes in an atomic operation.

In Part 51, we enter Module 6 to master Aggregation & Grouping: COUNT, SUM, AVG, MIN, MAX!