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.