Atomic State Synchronization with the MERGE Statement

Snippet

Atomic State Synchronization with the MERGE Statement

The MERGE statement provides a standard-compliant way to perform 'upsert' operations. It allows a single SQL statement to decide whether to update or insert data based on whether a join condition is met. This ensures atomicity and reduces the need for procedural logic or multiple round-trips to the database.

snippet.sql
sql
MERGE INTO inventory i 
USING updates u 
ON (i.product_id = u.product_id) 
WHEN MATCHED THEN 
    UPDATE SET i.stock = i.stock + u.change 
WHEN NOT MATCHED THEN 
    INSERT (product_id, stock) 
    VALUES (u.product_id, u.change);

Breakdown

MERGE INTO inventory i

Specifies the target table to be modified.

USING updates u ON (i.product_id = u.product_id)

Defines the data source and the criteria for matching records.

WHEN MATCHED THEN UPDATE SET...

Defines the action to take if the record already exists in the target.

WHEN NOT MATCHED THEN INSERT...

Defines the action to take if the record is missing in the target.

Previous snippet Next snippet

From your library

Atomic State Synchronization with the MERGE Statement

Related