The MERGE statement is used to make changes in one table based on values matched from another. It can be used to combine insert, update, and delete operations into one statement. The MERGE statement basically merges data from a source result set to a target table based on a condition that we specify and if the data from the source already exists in the target or not. The new SQL command combines the sequence of conditional INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record.
Syntax of the Merge statement
|
MERGE targetTable
Using sourceTable
ON mergeCondition
WHEN MATCHED
THEN updateStatement
WHEN MATCHED and othercondtion --optional
THEN updateStatement
WHEN NOT MATCHED BY TARGET
THEN insertStatement
WHEN NOT MATCHED BY SOURCE
THEN deleteStatement
|
The merge statement works using two tables, the source Table and target Table. The target Table is the table to be modified based in data contained within the source Table.
There will be three conditions
- MATCHED(Source & Target)
- NOT MATCHED BY TARGET
- NOT MATCHED BY SOURCE
MATCHED – these are rows satisfying the match condition. They are common to both the source and target tables.
NOT MATCHED – This is also known as NOT MATCHED BY TARGET; these are rows from the source table that didn’t match any rows in the target table.
NOT MATCHED BY SOURCE – these are rows in the target table that were never matched by a source record.
See the example:
Here I am having two tables known as EMP_Source and EMP_TARGET
Checking EMP_ID and EMP_NAME
If a match and EMP_ADD is not same then update EMP_ADD records in Target table.
If not match by the target, in this case, insert a record in Target table.
EMP_ID 2 address will be updated and set it to ‘PUNE’.
|
MERGE Emp_Target T
Using Emp_Source S
ON
T.EMP_ID = S.EMP_ID and
T.EMP_NAME=S.EMP_NAME
WHEN MATCHED and T.EMP_ADD != S.EMP_ADD
THEN
UPDATE SET T.EMP_ADD=S.EMP_ADD
WHEN NOT MATCHED BY TARGET
THEN
INSERT VALUES (S.EMP_ID,S.EMP_NAME,S.EMP_ADD);
|
Running this script.
See the result
Target has been updated from ‘Patna’ to ‘Pune’.
