15.00 - UPDATE (Temporal Form) - Teradata Database

Teradata Database Temporal Table Support

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

UPDATE (Temporal Form)

Purpose  

Modifies column values in existing rows of a temporal table.

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 update is current in the valid-time dimension if the target table supports valid time.

Note: A current UPDATE affects only current rows. Future rows with valid-time periods that do not overlap TEMPORAL_TIMESTAMP or TEMPORAL_DATE will not be updated.

If the target table does not support valid time, at least one of the referenced tables must be a table with valid time. The update is not a current update. 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 UDPATE statement, the default qualifier is CURRENT VALIDTIME.

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 update is sequenced in the valid-time dimension if the target table supports valid time.

If the target table does not support valid time, at least one of the referenced tables must be a table with valid time. The update is not a sequenced update. The VALIDTIME or SEQUENCED VALIDTIME qualifier is used to qualify rows from the referenced tables in the valid-time dimension. In the transaction-time dimension, open rows qualify.

A sequenced update on a target table that supports valid time sets the valid-time value to the intersection of the valid-time column value and period_expression.

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 update is nonsequenced in the valid-time dimension if the target table supports valid time.

If the target table does not support valid time, at least one of the referenced tables must be a table with valid time. The update is not a nonsequenced update. The NONSEQUENCED VALIDTIME qualifier is used to qualify rows from the referenced tables in the valid-time dimension. In the transaction-time dimension, open rows qualify.

NONTEMPORAL

that the update is nonsequenced in the valid-time dimension and nontemporal in the transaction-time dimension.

A nontemporal update treats the transaction-time column as a nontemporal column.

The table must support transaction time.

update_statement

conventional syntax for the UPDATE 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, unless the NONTEMPORAL qualifier is used.

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.

Required Privileges

The following privileges are required in addition to those privileges required for a conventional UPDATE statement:

  • If the UPDATE statement specifies the NONTEMPORAL qualifier, the NONTEMPORAL privilege is also required on the temporal table.
  • For a current or sequenced update to a table with valid time, the UPDATE privilege is required on the valid-time column of the table.
  • Usage Notes  

    Unless the NONTEMPORAL qualifier is specified, updates on temporal tables are always current in the transaction-time dimension.

    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.

    Current Updates

    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.

    A current update affects only rows that are valid at the current time. These are rows in valid-time or bitemporal tables with a PV that overlaps current time at the time of the update: Rows that have a valid-time period that contains or begins at TEMPORAL_TIMESTAMP (or TEMPORAL_DATE, depending on the type of the valid-time column).

    If additional search conditions are specified in the UPDATE statement, they are applied to these current rows. The search condition may specify conditions on both the valid-time and transaction-time columns.

    The following types of rows do not qualify for current updates:

  • Rows in valid-time or bitemporal tables with a PV that ends before or at current time. These rows are history rows in valid time, so do not qualify for a current update.
  • Rows in valid-time or bitemporal tables with a PV that begins after current time. These rows are future rows in valid time, so do not qualify for a current update.
  • Rows in bitemporal tables with a transaction-time period that ends before UNTIL_CLOSED (before 9999-12-31 23:59:59.999999+00:00). These rows are considered closed in transaction time, and are unavailable to most SQL. They are part of the automatic internal history of changes maintained by the database for tables with transaction time, and do not qualify for a current update.
  • A current update to a row can result in zero, one, or two additional rows being added to the database, depending on the relationship between the PV of the qualifying row and the current time, and on whether the table is a valid-time or bitemporal table.

    Current Updates to Qualifying Rows in Valid-Time Tables

    If the PV of the row contains the current time at the time of the update, the operation modifies one row and inserts a new row into the table:

  • The valid-time period for the original row, is set to end at the time of the update (TEMPORAL_TIMESTAMP or TEMPORAL_DATE, depending on the type of the valid-time column). No other column values are changed.
  • This means that the original row is no longer valid. It becomes a history row, showing the original column values that were valid before the update.

  • A copy of the original row is inserted which has the new values in the updated columns. The valid-time period of this row is set to begin at the time of the update, and end at the same time as the original row.
  • If the PV of the row begins at the time of the update:

  • The current update operation updates the row.
  • The PA of a current update begins at TEMPORAL_TIMESTAMP at the time of the update, and ends at UNTIL_CHANGED, an indefinite time in the future when the row is changed or deleted. Because this PA matches or contains the PV of the qualified row, the change is valid for the entire PV of the row. New rows do not need to be inserted in the database to account for row states that existed before or after the change.

    Current updates to Qualifying Rows in Bitemporal Tables

    Bitemporal tables include both a valid-time column and a transaction-time column. The results of a current update operation on a bitemporal table with respect to the valid-time column are the same as those for a valid-time table. Due to the transaction-time column, every row that is changed as a result of the current update generates an additional row in the database to track the change in transaction time by creating a snapshot of the row prior to the change:

    If the PV of the row contains the current time at the time of the update, the operation modifies one row and inserts two new rows into the table:

  • The transaction-time period for the original row is set to end at the time of the update (TT_TIMESTAMP), marking the row as closed in the transaction-time dimension. No other values are changed in the row.
  • This preserves the original row with the original values that existed before the modification, including the original period of validity. Because the row is closed in transaction-time, it becomes inaccessible to further modifications.

  • A copy of the original row is inserted. The row has the valid-time period set to end at the time of the update (TEMPORAL_TIMESTAMP or TEMPORAL_DATE, depending on the type of the valid-time column). No other column values are changed.
  • This means that the original row is no longer valid. It becomes a history row, showing the original column values that were valid before the update.

    The value of the transaction-time column is set to (TT_TIMESTAMP, UNTIL_CLOSED), as it is for any new row that is inserted to a table having a transaction-time dimension. The row is therefore open in transaction time, and remains accessible as a history row to valid-time SQL.

  • A copy of the original row, which has the new values in the updated columns, is inserted.
  • The valid-time period of this row is set to begin at the time of the update and end at the same time as the original row.

    The value of the transaction-time column is set to (TT_TIMESTAMP, UNTIL_CLOSED), as it is for any new row that is inserted to a table having a transaction-time dimension. The row is therefore open in transaction time, and remains accessible to valid-time SQL.

    If the PV of the row begins at the time of the update, the operation modifies one row and inserts one new row into the table:

  • The transaction-time period for the original row is set to end at the time of the update (TT_TIMESTAMP), marking the row as closed in the transaction-time dimension. No other values are changed in the row.
  • This preserves the original row with the original values that existed before the modification, including the original period of validity. Because the row is closed in transaction-time, it becomes inaccessible to further modifications.

  • The current update operation updates the row.
  • A copy of the original row is inserted. The row has the new values in the updated columns. The PA of a current update begins at TEMPORAL_TIMESTAMP at the time of the update, and ends at UNTIL_CHANGED, an indefinite time in the future when the row is changed or deleted. Because this PA matches or contains the PV of the qualified row, the change is valid for the entire PV of the row. New rows do not need to be inserted in the database to account for row states that existed before or after the change.

    The value of the transaction-time column is set to (TT_TIMESTAMP, UNTIL_CLOSED), as it is for any new row that is inserted to a table having a transaction-time dimension. The row is therefore open in transaction time, and remains accessible to valid-time SQL.

    Usage Notes

  • The value of TEMPORAL_TIMESTAMP used to stamp the valid-time column is the same for all rows produced as a result of a single update operation.
  • The value of TT_TIMESTAMP used to stamp the transaction-time column is the same for all rows produced as a result of a single update operation.
  • If an update to a qualified row does not actually change any column values in a row, temporal operations that close, open, and create new rows are not performed on the row. However, the activity count of the update operation includes these rows, and an update trigger qualifies these rows.
  • The modified and inserted rows must not violate any constraints on the table. If there are no uniqueness constraints, inserted rows are not checked for duplicates. If the table has any constraints defined, inserted rows are validated to ensure that the rows do not violate the constraints.
  • Sequenced Updates

    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.

    Nonsequenced Updates

    A nonsequenced update modifies the specified rows across all states or any state. A nonsequenced update ignores valid-time semantics when updating a row of a table with valid time.

    A nonsequenced update operates on only open rows for a table with transaction time. A nonsequenced update treats a valid-time column like a regular column for a table with valid time. For a valid-time table, a nonsequenced update does not create multiple rows like in a current or sequenced update.

    For a table with transaction time, a nonsequenced update of a row first closes out the existing qualified row and inserts a new row with the updated columns only when the column values change. If there are no changes made to the row, the existing row is not closed. For a valid-time table, a nonsequenced update modifies the existing qualified row like a regular update.

    Because a nonsequenced update permits updates to the valid-time column like a conventional Period column, the valid-time column can be used in the assignment list.

    All modifications on a transaction-time table or a bitemporal table cause changes to be recorded, regardless of whether the modifications are in the same transaction.

    A nonsequenced update that joins two or more tables is like a regular join. The valid-time column may participate in the join like a regular column.

    Nontemporal Updates

    Note: Rows that are closed in transaction time provide a history of all modifications and deletions on tables that have a transaction-time column. The automatic history that tables with transaction time provide can be used for regulatory compliance auditing, so these rows are generally inaccessible to DML modifications. Because NONTEMPORAL DML statements can modify closed rows, the special NONTEMPORAL privilege is required. For more information on the NONTEMPORAL privilege, see “NONTEMPORAL Privilege” on page 181.

    A nontemporal update is similar to a conventional update, but the transaction-time column is treated as any other column in the table. The transaction-time column values can be explicitly specified in the SET clause of the statement. A nontemporal update can be issued to update closed or open rows.

    The qualification condition in the UPDATE statement considers both the open and closed rows in the table. Additionally, for a table with valid time, both valid and no-longer-valid rows participate in the update. If the statement references multiple temporal tables, a nonsequenced form of join is performed on the tables.

    If the transaction-time column is modified, the following rules apply:

  • The beginning bound must not be greater than the system time at the time of the update.
  • The ending bound must be UNTIL_CLOSED or less than or equal to the system time at the time of the update.
  • Related Information

     

    For more information on...

    See...

    UPDATE statement

    SQL Data Manipulation Language