Summary -

In this topic, we described about the MERGE statement with detailed example.

MERGE statement is used with two tables like source table and target table. MERGE statement is used to modify target table rows or values with matched values on another source table. By using MERGE statement, we can perform UPDATE, INSERT, DELETE operations in one single MERGE statement. MERGE statement has three conditions are explained as follows –

  • WHEN MATCHED – This condition represents that the rows which are present in both tables. This condition is used when we need to update the rows in target table by using source table.
  • WHEN NOT MATCHED – This condition represents that the rows from source table which are not present in target table. WHEN NOT MATCHED is also called as WHEN NOT MATCHED BY TARGET. This condition is used when we need to INSERT source table rows in target table.
  • WHEN NOT MATCHED BY SOURCE – This condition represents that the rows from target table which are not present in source table. This condition is used to DELETE the rows in target table to match target table with source table.

Syntax -

MERGE targetable
USING source_table
ON MERGE_condition
WHEN MATCHED 
   THEN UPDATE statement
WHEN NOT MATCHED BY TARGET
   THEN INSERT statement
WHEN NOT MATCHED BY SOURCE
   THEN DELETE statement 

Example -

let us consider employee_details and employee_info tables are as follows -

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

employee_info -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 10000.00 1000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
010 Employee10 Salesman Employee4 2019-09-09 14000.00 1000

Scenario – Modifying target table like source table by using MERGE statement.

Requirement – Modifying employee_info table like employee_details table by inserting, updating, deleting rows in employee_info table by using MERGE statement. The query was as follows –

MERGE employee_info
USING employee_details
ON (target.emp_id = source.emp_id)

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

WHEN NOT MATCHED BY TARGET 
THEN INSERT (emp_id,emp_name,manager_id,designation,date_of_hire,
salary,dept_id)VALUES(source.emp_id,source.emp_name,source.manager_id,
source.designation,source.date_of_hire,source.salary,source.dept_id) 

WHEN NOT MATCHED BY SOURCE
THEN DELETE ; 

By executing above query, we can UPDATE the salary of Director employee, INSERT rows from employee_details, DELETE rows which are not in employee_details in employee_info. The output was as follows –

employee_info -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000