SQL - MERGE Statement in SQL

The MERGE statement in SQL is used to synchronize data between two tables or between a source dataset and a target table. It allows you to perform different operations such as INSERT, UPDATE, and sometimes DELETE based on whether a matching record already exists in the target table. Instead of writing separate SQL statements for each condition, the MERGE statement combines these operations into a single statement.

What Is the MERGE Statement?

Suppose a company has a Customers table that stores existing customer information. Every day, a new dataset containing updated customer information is received. Some customers in the new dataset already exist in the main table, while others are new.

Without MERGE, you might need to:

  1. Find whether a customer already exists.

  2. Update the existing customer.

  3. Insert the customer if no matching record exists.

  4. Optionally remove records that are no longer required.

The MERGE statement can handle these conditions together.

Basic Syntax

A commonly used form of the MERGE statement is:

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

Here:

  • target_table is the table that needs to be updated.

  • source_table contains the new or incoming data.

  • ON specifies the condition used to find matching records.

  • WHEN MATCHED defines what should happen when a matching record is found.

  • WHEN NOT MATCHED defines what should happen when there is no matching record.

Example

Consider a target table named Employees.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    Department VARCHAR(50),
    Salary DECIMAL(10,2)
);

Suppose the table contains:

EmployeeID EmployeeName Department Salary
101 Rahul IT 50000
102 Priya HR 45000
103 Arun Sales 40000

Now suppose a new table called EmployeeUpdates contains:

EmployeeID EmployeeName Department Salary
101 Rahul IT 55000
102 Priya Finance 48000
104 Sneha Marketing 42000

The new data contains updates for employees 101 and 102 and a completely new employee, 104.

A MERGE statement can synchronize this information:

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

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

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

After the operation, the target table will contain the updated information for employees 101 and 102, while employee 104 will be inserted as a new record.

WHEN MATCHED

The WHEN MATCHED clause is used when the record from the source table matches a record in the target table.

For example:

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

If employee 101 exists in both tables, the salary in the target table is updated using the salary from the source table.

Some database systems also support additional conditions:

WHEN MATCHED AND source.Salary > target.Salary THEN
    UPDATE SET target.Salary = source.Salary;

This means the update occurs only when the source salary is greater than the existing salary.

WHEN NOT MATCHED

WHEN NOT MATCHED is generally used when a source record does not have a corresponding record in the target table.

For example:

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

If employee 104 does not exist in the target table, the employee is inserted.

MERGE for Data Synchronization

One of the important uses of MERGE is data synchronization.

For example, an organization might receive customer data from an external application every night. The incoming data may contain:

  • Existing customers with changed information

  • New customers

  • Records that have not changed

Instead of manually comparing the two datasets, a MERGE operation can compare the source and target and apply the required changes.

This makes MERGE particularly useful in data integration and database maintenance tasks.

MERGE with Conditional Updates

MERGE can also be used when an update should happen only under specific conditions.

For example:

MERGE INTO Products AS target
USING ProductUpdates AS source
ON target.ProductID = source.ProductID

WHEN MATCHED AND source.Price <> target.Price THEN
    UPDATE SET target.Price = source.Price

WHEN NOT MATCHED THEN
    INSERT (ProductID, ProductName, Price)
    VALUES (
        source.ProductID,
        source.ProductName,
        source.Price
    );

Here, an existing product is updated only when its price is different from the incoming price.

MERGE and DELETE

Some SQL database systems support a WHEN MATCHED THEN DELETE form or related conditional deletion functionality.

For example:

WHEN MATCHED AND source.Status = 'Inactive' THEN
    DELETE

This can be useful when source data indicates that a particular record should be removed from the target table.

However, the exact MERGE syntax and supported clauses differ between database systems. Therefore, the syntax should always be checked against the specific SQL implementation being used.

Difference Between MERGE and Separate INSERT/UPDATE Statements

Without MERGE, the synchronization process may require several statements:

UPDATE Employees
SET Salary = 55000
WHERE EmployeeID = 101;

INSERT INTO Employees
(EmployeeID, EmployeeName, Department, Salary)
VALUES
(104, 'Sneha', 'Marketing', 42000);

With MERGE, the matching logic and the required actions can be placed into one statement.

This can make synchronization logic easier to organize, particularly when the source contains a large number of records.

Advantages of MERGE

MERGE provides several practical benefits.

1. Data synchronization

It is useful for synchronizing information between source and target tables.

2. Multiple operations in one statement

Depending on the SQL implementation, MERGE can combine operations such as updating existing rows and inserting new rows.

3. Conditional processing

Different actions can be performed depending on whether a record matches and whether additional conditions are satisfied.

4. Useful in ETL processes

MERGE can be useful when loading transformed or updated data into a target database.

5. Reduces repetitive comparison logic

Instead of repeatedly checking whether records exist before deciding what operation to perform, the matching condition can be specified directly in the MERGE statement.

Important Considerations

The MERGE statement is not implemented identically across all database management systems. SQL Server, Oracle, PostgreSQL, and other database systems can have differences in their MERGE syntax, supported clauses, and behavior.

Therefore, a MERGE statement written for one database system may require modifications before it can be used in another.

It is also important to make sure that the matching condition uniquely identifies the intended target record. If multiple source records match the same target record, some database systems may produce an error or unexpected behavior.

Real-World Applications

MERGE can be used in several real-world situations.

Customer database synchronization: Updating existing customer information and adding newly registered customers.

Inventory management: Updating stock quantities for existing products and inserting newly introduced products.

Employee management: Updating employee departments, salaries, or other details while adding new employees.

Data warehouse loading: Synchronizing incoming data with existing warehouse tables.

ETL pipelines: Applying transformed source data to a destination database.

Master data management: Keeping centralized records synchronized with information received from different systems.

Conclusion

The MERGE statement is a powerful SQL feature for synchronizing data between a source and a target. Its main purpose is to determine whether records already exist and then perform the appropriate operation, such as updating an existing record or inserting a new one. Some database systems also support conditional deletion through MERGE.

For students learning SQL, the key idea is simple: MERGE compares source data with target data and applies different actions depending on whether a match is found. It is especially useful in data integration, ETL operations, database synchronization, and large-scale data management.