Example: Current or Sequenced Delete from a Bitemporal Table - Analytics Database - Teradata Vantage

Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
eud1628112402879.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
fif1472244754791
lifecycle
latest
Product Category
Teradata Vantage™

The syntax of these operations is identical to the same kinds of deletions performed on valid-time tables:

  • To perform a current delete, use the CURRENT VALIDTIME qualifier in the DELETE statement.
  • To perform a sequenced delete, use the SEQUENCED VALIDTIME qualifier in the DELETE statement. (Using VALIDTIME alone as the qualifier is equivalent.)
There are two important ways that these kinds of deletions on bitemporal tables differ from those on valid-time tables:
  • Current and sequenced deletions on bitemporal tables affect only rows that are open in the transaction-time dimension.
  • Because rows are physically removed from bitemporal tables only when the NONTEMPORAL qualifier is used, rows deleted in SEQUENCED VALIDTIME are only deleted logically. The ending bound of their transaction-time period is changed from the value of UNTIL_CLOSED to the date or timestamp of the deletion, and the row becomes closed in the transaction-time dimension. The logically deleted row becomes a history row.

    The valid-time period remains unchanged for the logically deleted row. The deleted state of the row is reflected in the ending bound of the transaction time. However, similar to a SEQUENCED VALIDTIME DELETE on a valid-time table, if the period of validity of the original row extended beyond the period of applicability of the sequenced delete new rows are created that reflect the time periods for which the information was not deleted. The new rows have appropriately modified valid-time periods. These new rows are open in the transaction-time dimension, because their time periods were not included in the period of applicability of the deletion.