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

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).