DELETE Syntax Elements (Temporal Form) - 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™
CURRENT VALIDTIME
Specifies that the delete is current in the valid-time dimension if the target table supports valid time.
A current DELETE affects only current rows. Future rows with valid-time periods that do not overlap TEMPORAL_TIMESTAMP or TEMPORAL_DATE will not be deleted.
If the target table does not support valid time, at least one of the referenced tables must be a table with valid time. The delete is not a current delete. 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.
CURRENT DML modifications can cause serializability issues for concurrent transactions. See Potential Concurrency Issues with Current Temporal DML for information on avoiding these issues.
VALIDTIME and SEQUENCED VALIDTIME
Specifies that the delete 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 delete is not a sequenced delete. 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.
period_expression

Specifies 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.

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
Specifies that the delete 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 delete is not a nonsequenced delete. 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
Specifies that the delete is nonsequenced in the valid-time dimension and nontemporal in the transaction-time dimension.
The target table must support transaction time.
table_name
Specifies the name of the target table from which the delete operation is to remove rows.
[AS] correlation_name
Specifies an optional table alias name.
joined_table_name
Specifies the name of a joined table referenced in the WHERE clause.
WHERE condition
Specifies a condition for filtering the rows to be deleted.