15.00 - UPDATE (Temporal Upsert Form) - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

UPDATE (Temporal Upsert Form)

Purpose  

Updates column values in a specified row or, if the row does not exist, inserts it into the table with a specified set of initial column values.

Syntax  

Note: Temporal Table Support describes syntax that is especially relevant to temporal tables. Syntax that is not required, or that is not otherwise specific to temporal tables is generally not shown in this manual. For additional syntax, see SQL Data Definition Language, SQL Data Manipulation Language, and SQL Data Control Language.

Note: To ensure application portability to future ANSI standards for temporal SQL, Teradata recommends explicit specification of all temporal qualifiers.

 

Syntax Element …

Specifies …

CURRENT VALIDTIME

that the upsert is current in the valid-time dimension.

The CURRENT VALIDTIME qualifier is used to qualify rows from the referenced tables in the valid-time dimension. In the transaction-time dimension, open rows qualify.

If the session temporal qualifier is not set and the temporal qualifier is omitted from the upsert statement, the default qualifier is CURRENT VALIDTIME.

For a table that supports transaction time, the temporal qualifier in the transaction-time dimension is CURRENT TRANSACTIONTIME.

Caution:

CURRENT DML modifications can cause serializability issues for concurrent transactions. See Appendix C: “Potential Concurrency Issues with Current Temporal DML” for information on avoiding these issues.

VALIDTIME

SEQUENCED VALIDTIME

that the upsert is sequenced in the valid-time dimension if the target table supports valid time.

The target table must have a valid-time column.

A sequenced upsert on a target table that supports valid time sets the valid-time value to period_expression, or, if omitted, to PERIOD'(0001-01-01, UNTIL_CHANGED)' for a PERIOD(DATE) valid-time column or PERIOD '(0001-01-01 00:00:00.000000+00:00, UNTIL_CHANGED)' for a PERIOD(TIMESTAMP) valid-time column.

For a table that supports transaction time, the temporal qualifier in the transaction-time dimension is CURRENT TRANSACTIONTIME.

period_expression

the period of applicability for the DML statement.

The period of applicability must be a period constant expression that does not reference any columns, but can reference parameterized values and the TEMPORAL_DATE or TEMPORAL_TIMESTAMP built-in functions.

The period of applicability can also be a self-contained noncorrelated scalar subquery that is always nonsequenced in the time dimensions regardless of the temporal qualifier for the DML statement.

Note: If a period_expression is specified, the valid-time column cannot be specified or referenced anywhere in the query. If the valid-time column is a derived period column, the component columns cannot be specified or referenced anywhere in the query.

If period_expression is omitted, the period of applicability defaults to PERIOD'(0001-01-01, UNTIL_CHANGED)' for a PERIOD(DATE) valid-time column or PERIOD '(0001-01-01 00:00:00.000000+00:00, UNTIL_CHANGED)' for a PERIOD(TIMESTAMP(n) WITH TIME ZONE) valid-time column, where precision n and WITH TIME ZONE are optional.

NONSEQUENCED VALIDTIME

that the upsert is nonsequenced in the valid-time dimension if the target table supports valid time.

The target table must have a valid-time column.

For a table that supports transaction time, the temporal qualifier in the transaction-time dimension is CURRENT TRANSACTIONTIME.

upsert_statement

existing UPDATE (Upsert Form) statement syntax.

For details, see SQL Data Manipulation Language.

The following restrictions apply to the SET clause of the UPDATE portion of upsert_statement:

  • For a current or sequenced update on a table with valid time, the SET clause cannot reference the valid-time column as the name of the column whose data is to be updated.
  • For an update on a table with transaction time, the SET clause cannot reference the transaction-time column as the name of the column whose data is to be updated.
  • The SET clause cannot use CURRENT_DATE or CURRENT_TIMESTAMP in the expressions that are used to assign a value to the valid-time column.
  • For a table that has a PPI, the SET clause cannot reference a partitioning column.
  • Required Privileges

    UPDATE (Temporal Upsert Form) requires the same privileges as conventional UPDATE (Upsert Form).

    Usage Notes  

    The UPDATE portion of upsert follows the semantics described for the temporal form of UPDATE. The INSERT portion of upsert follows the semantics described for the temporal form of INSERT. All restrictions that apply to the conventional form of upsert also apply to the temporal form of upsert, with the exception of the relaxed restrictions documented in the following topics.

    If the upsert does not require any temporal operations, such as opening or closing a row, use a NONSEQUENCED VALIDTIME UPSERT. The SET clause can reference the valid-time column to set the valid-time value.

    All check, primary key, and temporal unique (current, sequenced, nonsequenced) constraints defined on the table are checked only on rows that are open in transaction time.

    DML operations on tables defined with NORMALIZE produce a normalized set of modified rows. Some unmodified rows may be deleted from the target table as a result of the normalization.

    When the target table is a normalized temporal table with transaction time, rows that are deleted as a result of the normalization are closed in transaction time.

    Qualifying a Row for Upsert

    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).
  • Note: Upserts are not supported for tables that do not have primary indexes or for column-partitioned tables.

    Modification of a Row for Upsert

    A current upsert results in a current update if the qualified current row is found; otherwise, it results in a current insert. For a table that has a PPI, a current update must not result in rows moving to different partitions.

    The insert portion of a current upsert can specify values for the valid-time or transaction-time column and the insert must be to the same partition as referenced by the update portion.

    In a sequenced upsert, if the qualified row overlaps with the specified (or default) period of applicability, a sequenced form of update is performed on that row; otherwise, a sequenced form of insert is performed. For a table that has PPI, the sequenced form of update must not result in rows moving to different partitions.

    The insert portion of a sequenced upsert can specify values for the valid-time or transaction-time column and the insert must be to the same partition as referenced by the update portion. If the insert portion of a sequenced upsert specifies a value for the valid-time column, the specified period of applicability in the temporal qualifier is ignored for the insert portion of the upsert.

    For a nonsequenced upsert, a nonsequenced update is performed if a row is found; otherwise, a nonsequenced insert is performed.

    Related Information

     

    For more information on...

    See...

    UPDATE statement (upsert form)

    SQL Data Manipulation Language