Teradata provide a way to perform Update & Insert operations simultaneously on a table from other table.

Teradata Upsert / Merge

Teradata provide a way to perform Update & Insert operations simultaneously on a table from other table. If update condition matches from another table then update will happen and if not then unmatched rows will be inserted in the table.

Creating Tables: Creating 2 tables with similar structure to understand the logic in details.

--Table 1
CREATE MULTISET VOLATILE TABLE mergetable(
	id INTEGER,
	name VARCHAR(100),
	indicator_flag CHAR(1)
)
PRIMARY INDEX(id)
ON COMMIT PRESERVE ROWS;

--Table 2
CREATE MULTISET VOLATILE TABLE othertable(
	id INTEGER,
	name VARCHAR(100),
	indicator_flag CHAR(1)
)
PRIMARY INDEX(id)
ON COMMIT PRESERVE ROWS;


Populating Tables: Inserting relevant data in newly created tables for understanding logic.

--Table 1:
INSERT INTO mergetable VALUES(1,'Teradata','N');
INSERT INTO mergetable VALUES(2,'Database',null);
INSERT INTO mergetable VALUES(3,'Oracle',null);
INSERT INTO mergetable VALUES(4,'Vertica',null);

--Table 2:
INSERT INTO othertable VALUES(1,'Teradata','Y');
INSERT INTO othertable VALUES(2,'Database','N');
INSERT INTO othertable VALUES(5,'DB2','Y');
INSERT INTO othertable VALUES(6,'MYSQL','Y');

MERGE INTO mergetable tgt
USING othertable src
     ON (src.id = tgt.id)
WHEN MATCHED THEN
UPDATE 
SET 
   name             = src.name,
   indicator_flag   = src.indicator_flag
WHEN NOT MATCHED THEN
INSERT 
(
   id,
   name,
   indicator_flag
)
VALUES(
   src.id,
   src.name,
   src.indicator_flag
);

Output:
 *** Merge completed. 4 rows affected.
     2 rows inserted, 2 rows updated, no rows deleted.

   Note: Primary index columns & partitioned columns(if table is partition) should always be present in matching condition(ON clause), otherwise MERGE will fail with below error.

*** Failure 5758 The search condition must fully specify the Target table primary index and partition column(s) and expression must match INSERT specification primary index and partition column(s).

• When 'WHERE' condition is satisfied in Update then Update will happen

  
  UPDATE mergetable
  SET   indicator_flag = 'N'
  WHERE id = 3
  ELSE 
     INSERT INTO mergetable (3, 'MongoDB', 'N');
  
  Output: 
  *** Update completed. One row changed. 
  

  
  
• When 'WHERE' condition is not satisfied in Update then Insert will happen

  
  UPDATE mergetable
  SET indicator_flag = 'N'
  WHERE id = 7
  ELSE 
      INSERT INTO mergetable (7, 'MongoDB', 'N');
  
  Output: 
  *** Insert completed. One row added.
  

  
  
• Points to be noted:
  1. If different tables(as shown below) will be used for UPDATE & INSERT clause in upsert operation then dml will fail with table mismatch error.

     
     UPDATE othertable
     SET indicator_flag = 'N'
     WHERE id = 7
     ELSE 
         INSERT INTO mergetable (7, 'MongoDB', 'N');
     
     Output: 
      *** Failure 5560 Table names are mismatched in the UPSERT statement.
     

     
     
  2. Upsert will fail if primary index values are different in 'WHERE' condition of UPDATE and INSERT clause. In the below example, Id is primary key.

     
     UPDATE mergetable
     SET  name = 'Teradata'
     WHERE id=1
     ELSE 
        INSERT INTO mergetable (3, 'MongoDB', 'N');
     
     Output: 
      *** Failure 5561 The primary index values are mismatched in the UPSERT statement.
     

     
     
  3. Upsert will fail (as shown below) if primary index column is not used in 'WHERE' clause of UPDATE.

     
     UPDATE tutorial_db.mergetable
     SET  id=1
     WHERE name = 'Teradata'
     ELSE 
        INSERT INTO tutorial_db.mergetable (3, 'MongoDB', 'N');
     
     Output: 
      *** Failure 5565 The UPDATE specified in the UPSERT statement is a complex update.