Assume a scenario where you need to update the existing records based on certain criteria conditions and non-updating records are inserted into the table. This update and insert combined in one SQL are called as UPSERT.
UPSERT is not an SQL command, but a loading technique where missing rows are inserted and existing records are updated.
TMPUP and MLOAD can both be used for this purpose.
In this recipe, we will create a sample table in the Teradata Database, and then we will update the existing records in the sample table and add new records if any. The MLOAD script will be used for this.
Connect to Teradata Database using SQLA or Studio. We will create the following table in one of the databases:
/*UPSERT create table*/
CREATE SET TABLE td_cookbook_nyse.mktprice ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
symbol CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
id varchar...