When to Use MERGE in Oracle SQL: Use Cases and Benefits

Last Updated on :December 6, 2025

The SQL MERGE statement allows you to perform multiple INSERT, UPDATE, and DELETE operations in a single statement. This makes it a powerful tool for handling situations where you need to either insert new data or update existing data in a table based on whether the data already exists.

When to Use the SQL Merge Statement

There are scenarios where you may need to decide between inserting a new row or updating an existing one, depending on the presence of a specific record in a table. For example, if you receive new product information, you might need to:

• Update details for products that already exist in your system.
• Insert records for new products that are not yet present.

Syntax of the Merge Statement

The basic syntax of the MERGE statement is as follows:

MERGE INTO [target_table]
USING [source_table | view | subquery]
ON ([condition])
WHEN MATCHED THEN
  [merge_update_clause]
WHEN NOT MATCHED THEN
  [merge_insert_clause];

Example: Using Merge with Product Data

Let’s consider an example where you have a product table (target table) and a product_stg table (staging table). The staging table contains product-related data, including both new and existing product information. You want to:

• Update the product table for records that match an existing productid.
• Insert new rows for records that do not exist in the product table.

Here is how the SQL MERGE statement is written:

MERGE INTO product p
USING product_stg p_stg
ON (p.productid = p_stg.productid)
WHEN MATCHED THEN
  UPDATE
  SET p.code = p_stg.code,
      p.name = p_stg.name,
      p.unitprice = p_stg.unitprice
WHEN NOT MATCHED THEN
  INSERT (p.productid, p.code, p.name, p.unitprice)
  VALUES (p_stg.productid, p_stg.code, p_stg.name, p_stg.unitprice);

Using Merge with DUAL Table

The DUAL table is a special one-row, one-column table often used in Oracle for testing expressions and assigning default values.  You can create a staging dataset on the fly using DUAL with UNION ALL to simulate multiple rows

This approach allows you to merge data into product without needing a physical product_stg table.

MERGE INTO product p
USING product_stg p_stg
ON (p.productid = p_stg.productid)
WHEN MATCHED THEN
  UPDATE
  SET p.code = p_stg.code,
      p.name = p_stg.name,
      p.unitprice = p_stg.unitprice
WHEN NOT MATCHED THEN
  INSERT (p.productid, p.code, p.name, p.unitprice)
  VALUES (p_stg.productid, p_stg.code, p_stg.name, p_stg.unitprice);

Practical Use Cases of MERGE statement

• Synchronizing data between operational and staging tables.
• Applying updates and inserts in data warehousing processes.
• Consolidating changes from multiple data sources into a single target table.
• Removing obsolete data during the merging process.

With the SQL MERGE statement, you can efficiently manage data updates and inserts, ensuring that your tables remain consistent and up-to-date with minimal code.