17.10 - Qualifying a Row for Upsert - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

In addition to the conditions specified in the UPDATE statement, the test for qualification of a row is also based on the overlap of the valid-time column value with the period of applicability. For a sequenced upsert, the row is considered for the update even if the period of validity is contained in the period of applicability; no row is inserted for the extra period of applicability.

The UPDATE portion of the upsert must qualify a single row.

When qualifying a row for a nonsequenced upsert, all of the restrictions that apply to the conventional form of upsert apply in the valid-time and transaction-time dimensions.

The following rules apply to temporal tables that have a partitioned primary index:

  • The UPDATE portion of a current upsert must qualify a single row from a single current partition.
  • The UPDATE portion of a sequenced upsert with an optional period of applicability and a matching PI value must also qualify a single row from single partition.
  • For a current or sequenced upsert, when partitioning is defined on the valid-time column, transaction-time column, or valid-time and transaction-time columns, the upsert can omit the equality condition on the valid-time and transaction-time columns. The equality condition can be specified on the bound functions that were used in the partitioning expression. The conditions IS UNTIL_CHANGED and IS UNTIL_CLOSED are also considered equality conditions on the END bound function (these conditions are valid only on the END bound function).
Upserts are not supported for tables that do not have primary indexes or for column-partitioned tables.