15.00 - DELETE (Temporal Form) - Teradata Database

Teradata Database Temporal Table Support

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

DELETE (Temporal Form)

Purpose  

Deletes or modifies one or more rows from 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 delete is current in the valid-time dimension if the target table supports valid time.

Note: 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.

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

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

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

the name of the target table from which the delete operation is to remove rows.

[AS] correlation_name

an optional table alias name.

joined_table_name

the name of a joined table referenced in the WHERE clause.

WHERE condition

a condition for filtering the rows to be deleted.

Required Privileges

The privileges required are the same as those required for a conventional DELETE statement.

If the DELETE statement specifies the NONTEMPORAL qualifier, the NONTEMPORAL privilege is also required on the temporal table.

Usage Notes  

Unless the NONTEMPORAL qualifier is specified, deletion on temporal tables with a transaction-time column is always limited to only those rows that are open in transaction time.

Current Delete

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.

For a table with valid time, current rows 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.

Note: 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.

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

 

IF the table is a …

AND the beginning bound is …

THEN the …

valid-time table and the element type of the valid-time column is DATE

equal to TEMPORAL_DATE

qualified row is physically deleted.

less than TEMPORAL_DATE

period of validity of the qualified row is modified with the ending bound set to TEMPORAL_DATE.

valid-time table and the element type of the valid-time column is TIMESTAMP

equal to TEMPORAL_TIMESTAMP

qualified row is physically deleted.

less than TEMPORAL_TIMESTAMP

period of validity of the qualified row is modified with the ending bound set to TEMPORAL_TIMESTAMP.

bitemporal table and the element type of the valid-time column is DATE

equal to TEMPORAL_DATE

qualified row is closed out in transaction time; that is, it is logically deleted.

less than TEMPORAL_DATE

qualified row is closed out in transaction time and a copy of the old row is inserted with the beginning bound of the period of validity set to the same value as the closed out row and the ending bound of the period of validity set to TEMPORAL_DATE.

bitemporal table and the element type of the valid-time column is TIMESTAMP

equal to TEMPORAL_TIMESTAMP

qualified row is closed out in transaction time; that is, it is logically deleted.

less than TEMPORAL_TIMESTAMP

qualified row is closed out in transaction time and a copy of the old row is inserted with the beginning bound of the period of validity set to the same value as the closed out row and the ending bound of the period of validity set to TEMPORAL_TIMESTAMP.

Sequenced Delete

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.

Nonsequenced Delete

A nonsequenced DELETE statement deletes the specified rows across all states. This ignores valid-time semantics when deleting rows from a table with valid time.

A nonsequenced delete operates on open rows only. A nonsequenced delete treats a valid-time column like a regular column.

For a bitemporal table, a nonsequenced delete of a row closes out the existing qualified row. For a valid-time table, the nonsequenced delete is like a conventional delete statement that physically deletes the qualified row.

Nontemporal Delete

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 delete, also referred to as vacuuming the table, physically deletes the qualifying rows from the table.

Caution:

The best practice is to back up the data before performing a nontemporal delete. Tracking of any deleted rows will be lost.

All rows, both open and closed, are considered for qualification conditions specified in the DELETE statement. If multiple transaction-time tables are referenced, they are joined using nonsequenced select semantics.

If the table being deleted has valid time, both valid and rows that are no longer valid are considered for qualification conditions specified in the query.

The only difference between a nonsequenced delete and a nontemporal delete on a table with transaction time is that a nonsequenced delete performs a logical delete of rows whereas a nontemporal delete performs a physical delete of rows.

Related Information

 

For more information on...

See...

DELETE statement

SQL Data Manipulation Language

deleting rows from temporal tables

“Modifying Temporal Tables” on page 200