The MERGE statement in SQL is used to perform "upsert" operations, which means it allows you to insert new records if they do not exist or update existing records if they do. The MERGE statement combines the functionality of INSERT, UPDATE, and DELETE.
Here's a general syntax for using the MERGE statement:
MERGE INTO target_table AS target
USING source_table AS source
ON target.key_column = source.key_column
WHEN MATCHED THEN
UPDATE SET target.column1 = source.column1,
target.column2 = source.column2
WHEN NOT MATCHED THEN
INSERT (target.key_column, target.column1, target.column2)
VALUES (source.key_column, source.column1, source.column2);
Explanation:
1. MERGE INTO: Specifies the target table that you want to merge data into.
2. USING: Specifies the source data that you want to use for the merge. This can be a table, a view, or a derived table.
3. ON: Defines the condition that determines whether the records in the target and source tables match.
4. WHEN MATCHED THEN: Specifies what to do when a match is found (i.e., the record already exists). Usually, you'll want to perform an UPDATE.
5. WHEN NOT MATCHED THEN: Specifies what to do when no match is found (i.e., the record does not exist). Typically, you'll want to perform an INSERT.
Example:
Let's say you have a customers table and you want to update existing customers or add new ones based on data from a new_customers source table.
MERGE INTO customers AS target
USING new_customers AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
UPDATE SET target.customer_name = source.customer_name,
target.contact_info = source.contact_info
WHEN NOT MATCHED THEN
INSERT (customer_id, customer_name, contact_info)
VALUES (source.customer_id, source.customer_name, source.contact_info);
In this example:
- If a row in new_customers has a customer_id that exists in customers, the corresponding customer_name and contact_info fields in customers will be updated.
- If a row in new_customers has a customer_id that does not exist in customers, a new row will be inserted into customers with the data from new_customers.
Important Notes:
1. Ensure you have appropriate keys defined when using the MERGE statement to avoid unexpected results.
2. The behavior of MERGE can vary between different SQL database management systems (DBMS), so always refer to the specific documentation for your DBMS for any nuances or additional options available.
3. Always test your MERGE statements in a development or testing environment before running them in production to prevent unintended data changes.