17.10 - Example: Current Delete from a Valid-Time Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

To perform a current delete, use the CURRENT VALIDTIME qualifier in the DELETE statement.

For a table with valid time, current rows qualify for deletion. Depending on the period of validity of a qualified row and whether the table also supports transaction time, the delete operation may physically delete a row, logically delete a row, modify the period of validity for a row, or logically delete a row and create a new row. Consider the following data in the Policy table:

   SELECT Policy_ID, Customer_ID, Validity
   FROM Policy
   WHERE Policy_Type = 'AU';

   Policy_ID  Customer_ID  Validity
   ---------  -----------  ------------------------
      497201    304779902  ('05/02/14', '06/02/13')
      540944    123344567  ('07/02/03', '08/02/02')
      541077    766492008  ('09/12/21', '99/12/31')
      541145    616035020  ('09/12/03', '10/12/01')
      541008    246824626  ('09/10/01', '99/12/31')

Suppose the value of TEMPORAL_DATE is the following:


   Temporal Date

The following current DELETE statement physically deletes the qualified row from the table because the beginning bound of the period of validity is equal to TEMPORAL_DATE:

   FROM Policy
   WHERE Policy_ID = 541077;