17.05 - Definition of Upsert - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

A simple upsert is a standalone update operation for which a subsequent standalone insert operation is coded that is performed only if the specific row to be updated is found not to exist. These actions require the performance of two individual SQL requests in the case where no row is found to update. This was the only means of performing a conditional insert on an update failure prior to the introduction of the upsert form of the UPDATE statement.

The more high-performing atomic upsert operation, represented by the upsert form of the UPDATE statement, is a single SQL request that includes both UPDATE and INSERT functionality. The specified update operation performs first, and if it fails to find a row to update, then the specified insert operation performs automatically.