Teradata provide multiple ways to Update data in a table using another table.

Teradata: Update Using Another Table

There are many scenarios where it is required to update a table either directly or using another table.

➠ Sample Table and Data

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

Table 1
CREATE MULTISET VOLATILE TABLE updatetable(
    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 updatetable VALUES(1,'Teradata',null);
INSERT INTO updatetable VALUES(2,'Database',null);
INSERT INTO updatetable VALUES(3,'Oracle',null);
INSERT INTO updatetable 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');

➠ Basic update : Update hardcode values to a table

UPDATE updatetable 
SET indicator_flag='Y'
WHERE id=4;

Output:
*** Update completed. One row updated.

➠ Multicolumn update : Update multiple column of a table

UPDATE updatetable 
SET indicator_flag='Y',
    name='Hive'
WHERE id=4;

Output:
*** Update completed. One row updated.

➠ Different approaches can be used to update data from one table using other table.

Approach 1 : update with the help of other table using join filter.

update updatetable 
from othertable as tempTableAlias
set indicator_flag = tempTableAlias.indicator_flag
where updatetable.id = tempTableAlias.id;

Output:
*** Update completed. Two row updated.


Approach 2 : Update with the help of other table using join filter.

UPDATE tempAggr
FROM updatetable as tempAggr,
     othertable as tempTableAlias
SET indicator_flag = tempTableAlias.indicator_flag
WHERE tempAggr.id = tempTableAlias.id;

Output:
*** Update completed. Two row updated.


Approach 3 : Update with the help of other table using Merge join.

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

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

Visit Teradata Upsert page to learn more about MERGE operation.