Oracle SQL Merge Statement

Last Updated on :March 4, 2021

SQL merge will allow you to perform multiple INSERT, UPDATE and DELETE in a single statement.

There are certain situation where you may want to insert a new row into a table or update an existing row depending on whether data already exists or not in the table.

Syntax of merge statement is as follows

MERGE INTO [table_name]
USING [table , view or Subquery]
ON ([condition])
WHEN MATCHED THEN [merge_update_clause]
WHEN NOT MATCHED THEN [merge_insert_clause]
[error_logging_clause]

Example

In below merge statement we have used product table and product_stg (staging table)

You may receive product related data that contains product information that are already present in system along with new product information those are newly introduced

You can write a SQL merge statement that reads each records from staging table and issue either insert or update on product table depending whether productid already exists or not in product 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
);

Lets discuss how above merge statement executes

• Lines 1-2 : For each row in product_stg table check whether productid exists in product table or not
• Lines 3-7 : If productid already exists in product table then update the matching row in product table using data from product_stg table
• Lines 8-22 : If there is no matching then insert new rows into product table using data from product_stg table