MERGE INTO Statement

The MERGE INTO statement 'merges' a source row into a target table based on whether any rows in the target table match a specified condition with the source row.

MERGE INTO replicates the functionality of the Upsert capability provided by the UPDATE with INSERT option. This option updates a row if it exists and inserts the row if it does not exists.

Example 1:

Add a new row for Department 700 using the MERGE INTO form of Upsert. 

MERGE INTO department
USING VALUES (700,'Shipping',800000.00)
                      AS Dept (deptnum, dept_name, budgamt)
ON Dept.deptnum = dept_num
WHEN MATCHED THEN UPDATE
SET budget_amount = Dept.budgamt
WHEN NOT MATCHED THEN INSERT
VALUES (Dept.deptnum, Dept.dept_name,Dept.budgamt,NULL);

SELECT  dept_num AS Dept
, department_name (CHAR(10))AS Dept_Name
, budget_amount
, manager_employee_number
FROM department
WHERE  dept_num  = 700;

  Dept  Dept_Name    budget_amount  manager_employee_number
  ------  ----------      ------------------   -----------------------
   700  Shipping        800000.00                        ?

Things to notice about this example:
This example does an insert of a 'Shipping' department row. 
The USING clause defines the source row to be merged. 
The AS clause defines the name of the temporary source table and columns which contain the source row. This is also referred to as the 'correlation table'. 
The ON clause followed by condition returns the matching rows in both the source and target tables.
It must use the primary index of the target table. 
If the Primary Index is non-unique, additional qualifying conditions are required to reduce the test to a single target row.

Example 2:

Update the budget for Department 700 to be $9,900,000 using the MERGE INTO form of Upsert. 

MERGE INTO department
USING VALUES (700,'Shipping',9900000.00) AS Dept (deptnum, dept_name, budgamt)
ON Dept.deptnum = dept_num
WHEN MATCHED THEN UPDATE
SET budget_amount = Dept.budgamt
WHEN NOT MATCHED THEN INSERT
VALUES (Dept.deptnum, Dept.dept_name,Dept.budgamt,NULL);

SELECT  dept_num  AS Dept
, department_name (CHAR(10))AS Dept_Name
, budget_amount
, manager_employee_number
FROM department
WHERE  dept_num  = 700;

  Dept  Dept_Name    budget_amount  manager_employee_number
  ------  ------------    ------------------   ---------------------------------
  700    Shipping       9900000.00                        ?

Things to notice about this example:
This example does an update of the previously inserted 'Shipping' department row. 



1 comment:

  1. MERGE INTO ...
    ...
    WHERE A = 1
    what happened to that? why can't I do that???

    ReplyDelete