what is UPSERT statement in teradata and how it works?

Question

How the upsert command is different from update command and how it works please make me understand with example and syntax.

Answer 1

UPSERT command inserts rows that don’t exist and updates the rows that do exist. In teradata, it can be achieved using MERGE or UPDATE-ELSE-INSERT.

UPDATE-ELSE-INSERT syntax:

    UPDATE department
SET budget_amount = 80000
WHERE department_number = 800 ELSE
  INSERT INTO department(department_number, department_name, budget_amount, manager_employee_number)
VALUES(800,
       'Test Dept',
       80000,
       NULL);

UPDATE-ELSE-INSERTis a teradata feature.

MERGE syntax:

MERGE INTO DEPARTMENT USING
VALUES(700,
       '    Test Department',
       80000) AS dept(dept_num, dept_name, budget_amt) ON dept.dept_num = department_number WHEN MATCHED THEN
UPDATE
SET budget_amount = dept.budget_amt WHEN NOT MATCHED THEN
INSERT
VALUES(dept_num,
       dept_name,
       budget_amt,
       NULL);

Hope this will help.