SQL Merge Statement- Best example

• The MERGE statement is a single command that combines the ability to update or insert rows into a table by conditionally deriving the rows to be updated or inserted from one or more sources. 
• It is most frequently used in data warehouses to move large amounts of data but its use is not limited to only data warehouse environments. 
• The big value-add this statement provides is that you have a convenient way to combine multiple operations into one. This allows you to avoid issuing multiple INSERT, UPDATE, and DELETE statements. 

Syntax of Merge statement:

MERGE INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];

Best example:

MERGE INTO dept60_bonuses b USING
  ( SELECT employee_id,
           salary,
           department_id
   FROM employees
   WHERE department_id = 60) e ON (b.employee_id = e.employee_id) WHEN matched THEN
UPDATE
SET b.bonus_amt = e.salary * 0.2
WHERE b.bonus_amt = 0
  DELETE WHERE (e.salary > 7500) WHEN NOT matched THEN
  INSERT (b.employee_id,
          b.bonus_amt)
  VALUES (e.employee_id,
          e.salary * 0.1) WHERE (e.salary < 7500);