Merging Data with SQL MERGE

When working with databases, there may come a time when you need to combine data from different sources or update existing data based on new information. That’s where SQL’s MERGE statement comes into play. The MERGE statement, also known as UPSERT, is a powerful command that allows you to merge two tables by inserting, updating or deleting rows in one table based on conditions met in another table.

Let’s start with a basic understanding of the MERGE command. The MERGE statement is used to make changes in one table based on values matched from another table. Essentially, it takes two tables – the target table (the table that needs to be updated) and the source table (the table containing new data) – and applies INSERT, UPDATE, or DELETE operations accordingly.

The general syntax of the MERGE statement looks like this:

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