MERGE Statement in SQL

The MERGE statement, often referred to as "UPSERT" (update or insert), is used to modify a target table based on the results of a source table. It allows you to combine multiple operations (INSERT, UPDATE, DELETE) into a single statement. The idea is that based on a given condition, you can decide whether to update existing records, insert new records, or delete records.

This functionality is particularly useful for maintaining data synchronization between two tables.

However, it's important to note that not all databases support the MERGE statement. For example, SQL Server and Oracle support it, but MySQL doesn't (MySQL offers INSERT ... ON DUPLICATE KEY UPDATE as an alternative).

Basic Syntax:

MERGE INTO target_table USING source_table
ON (condition)
WHEN MATCHED THEN
    UPDATE SET column1 = value1, column2 = value2, ...
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ... ) VALUES (value1, value2, ...);

Example:

Imagine we have a target table of products with columns product_id, product_name, and quantity, and a source table with new data for these products.

We want to:

Update the quantity in the target table for products that exist in the source table.
Insert new products from the source table if they don't exist in the target table.

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

More Features:

You can also use the MERGE statement to delete rows. For instance, you might want to delete rows from the target table that don't have a match in the source table:

MERGE INTO target AS T
USING source AS S
ON (T.product_id = S.product_id)
WHEN MATCHED THEN 
    UPDATE SET T.quantity = S.quantity
WHEN NOT MATCHED BY TARGET THEN
    INSERT (product_id, product_name, quantity)
    VALUES (S.product_id, S.product_name, S.quantity)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Points to Remember:

Atomicity: The MERGE statement is atomic. That is, it will all succeed or it will all fail. You won't end up in a situation where some rows are updated while others aren't.
Performance: The MERGE statement can be more efficient than executing an UPDATE followed by an INSERT because the rows are accessed only once.
Locking: Depending on the RDBMS and the volume of data, MERGE might lock the table. Ensure you understand the implications and test performance in a non-production environment.

Always remember to use the MERGE statement with caution, especially if you're modifying a large number of rows. Testing is essential before executing the statement in a production environment.

How to Use MERGE in SQL:

MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.column1 = source.column1, target.column2 = source.column2
WHEN NOT MATCHED THEN
    INSERT (id, column1, column2) VALUES (source.id, source.column1, source.column2);

Performing Upserts with the SQL MERGE Statement:

Upsert (Insert or Update) based on a matching condition.

MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.column1 = source.column1
WHEN NOT MATCHED THEN
    INSERT (id, column1) VALUES (source.id, source.column1);

Merging Data from Source to Target in SQL:

Merge data from a source table to a target table.

MERGE INTO target_table AS target
USING source_table AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN
    UPDATE SET target.email = source.email
WHEN NOT MATCHED THEN
    INSERT (user_id, email) VALUES (source.user_id, source.email);

Using Conditions in the SQL MERGE Statement:

Apply conditions to control update or insert operations.

MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id AND source.column3 = 'Condition'
WHEN MATCHED THEN
    UPDATE SET target.column1 = source.column1
WHEN NOT MATCHED THEN
    INSERT (id, column1) VALUES (source.id, source.column1);

Handling Conflicts with the MERGE Statement:

Handle conflicts using additional conditions or actions.

MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED AND source.column2 <> target.column2 THEN
    UPDATE SET target.column2 = source.column2
WHEN NOT MATCHED THEN
    INSERT (id, column1, column2) VALUES (source.id, source.column1, source.column2);

Comparing MERGE with INSERT and UPDATE in SQL:

MERGE simplifies conditional inserts and updates in a single statement.

-- Equivalent INSERT and UPDATE
INSERT INTO target_table (id, column1, column2)
VALUES (source.id, source.column1, source.column2)
ON DUPLICATE KEY UPDATE column1 = source.column1, column2 = source.column2;

Merging Data in SQL Tables with MERGE:

Merge data from multiple source tables to a single target table.

MERGE INTO target_table AS target
USING (
    SELECT user_id, email FROM source_table1
    UNION
    SELECT user_id, email FROM source_table2
) AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN
    UPDATE SET target.email = source.email
WHEN NOT MATCHED THEN
    INSERT (user_id, email) VALUES (source.user_id, source.email);

Logging Changes During a MERGE Operation in SQL:

Use output clauses to log changes.

MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.column1 = source.column1
WHEN NOT MATCHED THEN
    INSERT (id, column1) VALUES (source.id, source.column1)
OUTPUT $action, inserted.*;