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.

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 

let us consider employee_details and employee_info tables are as follows -

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 –