sql / expert
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
1
2
3
4
5
6
7
8
MERGE INTO inventory iUSING updates uON (i.product_id = u.product_id)WHEN MATCHED THENUPDATE SET i.stock = i.stock + u.changeWHEN NOT MATCHED THENINSERT (product_id, stock)VALUES (u.product_id, u.change);
Breakdown
1
MERGE INTO inventory i
Specifies the target table to be modified.
2
USING updates u ON (i.product_id = u.product_id)
Defines the data source and the criteria for matching records.
3
WHEN MATCHED THEN UPDATE SET...
Defines the action to take if the record already exists in the target.
4
WHEN NOT MATCHED THEN INSERT...
Defines the action to take if the record is missing in the target.