17.10 - Sequenced Updates - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1182-171K
Language
English (United States)

A sequenced update modifies the specified rows at each point in time that is covered in the period of applicability. That is, rows whose period of validity overlaps the period of applicability are modified for the overlapping portion.

A sequenced update can modify current, history, or future rows in the valid-time dimension depending on the selection time period (period of applicability) specified in UPDATE statement. For bitemporal tables, which include a transaction-time dimension, only rows that are open in transaction time can qualify for the sequenced update.

If the columns modified in the SET clause do not change the values for a row, the row is not changed with any temporal update semantics. The activity count of the update includes the row and an update trigger qualifies such a row but the modification semantics that close, open, or create new rows are not performed.

Only open rows whose period of validity overlaps the period of applicability of the sequenced update are candidates for the update. Optionally, additional qualifications can be placed on the values of valid-time and transaction-time columns to further filter the rows that will qualify for the update.

A sequenced update of a row in a valid-time table results in the modification of the old row and, potentially, the insertion of new rows, depending on the relationship between the period of validity of the row and the period of applicability of the update.

A sequenced update of a row in a bitemporal table first closes out the old row in transaction time. A copy of the row is made, open in transaction time, and modifications are made appropriate to the update syntax. These modifications can include simply updating the row, or splitting the row into two or three rows, depending on the relationship between the period of validity of the row and the period of applicability specified by the update statement.

The following table describes the sequenced update operation in a table with valid time.

IF the period of applicability of the update … THEN …
is contained within the period of validity of a row the row qualifies for the update, but only during the portion of the period of validity that overlaps the period of applicability of the update.
Because the row values are updated for only a portion of the original row period of validity, the original row values remain valid before and after the period of applicability of the update. Therefore, the update operation results in three rows:
  • The original row that qualified for the update is modified to have the valid time period end at the beginning of the period of applicability of the update.
  • A new row is inserted with the updated values. Its valid time period reflects the entire period of applicability of the update.
  • A new row is inserted with the same values as the original row that qualified for the update. However, the valid-time period is set to reflect the portion of the original row valid-time period that remains after the period of applicability of the update. The valid-time period begins at the end of the update period of applicability, and ends at the time the original row valid-time period ended.
overlaps the period of validity such that the beginning bound of the period of applicability is between the beginning and ending bounds of the period of validity the update results in one new row and one old existing row:
  • The new row contains the modified columns with its period of validity set to the portion that is common between the period of applicability and the period of validity.
  • The period of validity of the existing row is set to the portion of the period of validity that exists before the beginning of the period of applicability.
overlaps the period of validity such that the ending bound of the period of applicability is between the beginning and ending bounds of the period of validity the update results in one new row and one old existing row:
  • The new row contains the modified columns and the period of validity is set to the portion that is common between the period of applicability and the period of validity.
  • The period of validity of the old existing row is set to the portion of the period of validity that exists after the ending of the period of applicability.
contains the period of validity, including the case where the period of applicability equals the period of validity the existing row is updated for the specified columns. There is no change in the period of validity.

Temporal constraints that are defined on a table being updated apply to both the existing row that is modified and to the rows that are newly inserted.