SQL

MERGE in SQL

🔄 The MERGE statement in SQL is used to insert, update, or delete data in a single statement by comparing a target table with a source table or query. It is commonly used for data synchronization, ETL (Extract, Transform, Load) processes, data warehousing, and keeping tables up to date.

📖 What is the MERGE Statement?

The MERGE statement compares rows in a target table with rows from a source table or query. Based on whether rows match, SQL can perform different actions such as updating existing rows, inserting new rows, or deleting rows.

Information

A single MERGE statement can replace multiple INSERT, UPDATE, and DELETE statements, making synchronization tasks simpler and more efficient.

🎯 Why Use MERGE?

MERGE is ideal when data must be synchronized between two datasets.

📌 Synchronize two tables.
📌 Update existing records.
📌 Insert new records automatically.
📌 Optionally delete obsolete records.
📌 Simplify ETL and data migration processes.

📊 Sample Tables

Target table: Employees

EmployeeID	Name	Department	Salary
101	Alice	IT	70000
102	Bob	HR	55000
103	Charlie	Finance	65000

Source table: EmployeeUpdates

EmployeeID	Name	Department	Salary
101	Alice	IT	75000
103	Charlie	Finance	68000
104	David	Marketing	60000

📝 Basic Syntax

MERGE Syntax

MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id

WHEN MATCHED THEN
    UPDATE SET ...

WHEN NOT MATCHED THEN
    INSERT (...)
    VALUES (...);

💡 Basic MERGE Example

Update existing employees and insert new employees from the source table.

Update and Insert with MERGE

MERGE INTO Employees AS target
USING EmployeeUpdates AS source
ON target.EmployeeID = source.EmployeeID

WHEN MATCHED THEN
    UPDATE SET
        target.Name = source.Name,
        target.Department = source.Department,
        target.Salary = source.Salary

WHEN NOT MATCHED THEN
    INSERT (EmployeeID, Name, Department, Salary)
    VALUES (
        source.EmployeeID,
        source.Name,
        source.Department,
        source.Salary
    );

📊 Result After MERGE

EmployeeID	Name	Department	Salary
101	Alice	IT	75000
102	Bob	HR	55000
103	Charlie	Finance	68000
104	David	Marketing	60000

🗑️ MERGE with DELETE

Some database systems allow deleting target rows that do not exist in the source table.

MERGE with DELETE

MERGE INTO Employees AS target
USING EmployeeUpdates AS source
ON target.EmployeeID = source.EmployeeID

WHEN MATCHED THEN
    UPDATE SET
        target.Salary = source.Salary

WHEN NOT MATCHED BY TARGET THEN
    INSERT (EmployeeID, Name, Department, Salary)
    VALUES (
        source.EmployeeID,
        source.Name,
        source.Department,
        source.Salary
    )

WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Important

Clauses such as WHEN NOT MATCHED BY SOURCE are not supported by every SQL database. Always verify the syntax for your database system.

📈 MERGE Using a Query as the Source

The source of a MERGE operation can also be a SELECT query instead of a physical table.

MERGE Using a SELECT Query

MERGE INTO Employees AS target
USING
(
    SELECT EmployeeID,
           Name,
           Department,
           Salary
    FROM NewEmployees
) AS source
ON target.EmployeeID = source.EmployeeID

WHEN MATCHED THEN
    UPDATE SET
        target.Salary = source.Salary

WHEN NOT MATCHED THEN
    INSERT (EmployeeID, Name, Department, Salary)
    VALUES (
        source.EmployeeID,
        source.Name,
        source.Department,
        source.Salary
    );

⚖️ MERGE vs INSERT vs UPDATE

Feature	MERGE	INSERT	UPDATE
Add New Rows	✅ Yes	✅ Yes	❌ No
Modify Existing Rows	✅ Yes	❌ No	✅ Yes
Delete Rows	Some databases support it.	❌ No	❌ No
Typical Use	Synchronize tables.	Add new records.	Modify existing records.

💼 Real-World Example

A payroll system receives updated employee information every night. Instead of running separate INSERT and UPDATE statements, a single MERGE statement synchronizes the employee master table with the latest data.

Payroll Synchronization

MERGE INTO Employees AS target
USING PayrollUpdates AS source
ON target.EmployeeID = source.EmployeeID

WHEN MATCHED THEN
    UPDATE SET
        target.Salary = source.Salary

WHEN NOT MATCHED THEN
    INSERT (EmployeeID, Name, Department, Salary)
    VALUES (
        source.EmployeeID,
        source.Name,
        source.Department,
        source.Salary
    );

⚠️ Database Compatibility

Database	MERGE Support
SQL Server	✅ Supported
Oracle	✅ Supported
PostgreSQL	✅ Supported (PostgreSQL 15 and later)
MySQL	❌ Not supported directly. Use INSERT ... ON DUPLICATE KEY UPDATE or similar techniques.
SQLite	❌ Not supported directly.

⚠️ Common Mistakes

❌ Matching rows using incorrect join conditions.
❌ Assuming every database supports identical MERGE syntax.
❌ Updating primary key values unintentionally.
❌ Ignoring duplicate rows in the source data.

Warning

If multiple source rows match the same target row, many database systems will return an error. Ensure the source data uniquely identifies each target row.

⚠️ Best Practices

Best Practice

Use MERGE for synchronization tasks, ensure the matching condition uniquely identifies rows, validate source data before merging, test the statement with sample data first, and review your database's specific implementation because MERGE syntax and features vary across SQL vendors.

🚀 Key Points to Remember

📌 MERGE combines insert and update operations into a single statement.
📌 Some databases also support deleting unmatched target rows.
📌 It compares a source dataset with a target table.
📌 It is widely used in ETL, reporting, and data synchronization.
📌 Database support and syntax vary between SQL implementations.
📌 Ensure matching keys uniquely identify rows to avoid errors.

>>"The MERGE statement synchronizes data efficiently by combining insert, update, and, in some systems, delete operations into one SQL command."

Summary

✅ The MERGE statement is a powerful SQL feature for synchronizing data between two datasets. It simplifies complex data maintenance tasks by combining multiple operations into a single statement, making it especially valuable for ETL processes, data warehousing, and enterprise database applications.