MERGE in Teradata

MERGE in Teradata:

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