Sequenced Delete - 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™

For a table with valid time, rows that overlap with the period of applicability qualify for deletion. Any additional search conditions are applied only on these rows. The conditions in the WHERE clause or join ON conditions can be specified on valid-time or transaction-time columns.

Sequenced delete on a table with transaction time operates only on open rows.

The following table describes the sequenced delete operation of a qualified row.

IF the table is a … AND the period of applicability … THEN …
valid-time table contains the period of validity of the qualified row, including the case where they are equal the qualified row is physically deleted.
does not contain the period of validity of the qualified row If a portion of the period of validity exists before the beginning of the period of applicability, a copy of the row is inserted with the beginning bound of its period of validity set to the same value as the qualified row and the ending bound set to the beginning bound of the period of applicability.

If a portion of the period of validity exists after the end of the period of applicability, a copy of the row is inserted with the ending bound of its period of validity set to the same value as the qualified row and the beginning bound set to the ending bound of the period of applicability.

Note that two rows can be inserted if the preceding conditions are both true.

bitemporal table contains the period of validity of the qualified row, including the case where they are equal the qualified row is closed out (logically deleted) in transaction time.
does not contain the period of validity of the qualified row the qualified row is closed out in transaction time.

If a portion of the period of validity exists before the beginning of the period of applicability, a copy of the row is inserted with the beginning bound of its period of validity set to the same value as the qualified row and the ending bound set to the beginning bound of the period of applicability.

If a portion of the period of validity exists after the end of the period of applicability, a copy of the row is inserted with the ending bound of its period of validity set to the same value as the qualified row and the beginning bound set to the ending bound of the period of applicability.

Note that two rows can be inserted if the preceding conditions are both true.