17.05 - Example: Sequenced Delete from a Valid-Time Table - Teradata Database

Teradata Vantage™ - Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1182-170K

To perform a sequenced delete, use the VALIDTIME or SEQUENCED VALIDTIME qualifier in the DELETE statement.

For a table with valid time, any row with a period of validity that overlaps with the period of applicability qualifies for deletion. The delete operation may physically delete a row, logically delete a row, modify the period of validity for a row, or delete a row and create a new row. Consider the following data in the Policy table:

   NONSEQUENCED VALIDTIME
   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')

The following sequenced DELETE statement physically deletes one row from the table. The period of validity for policy 540944 (PERIOD '(2007-02-03, 2008-02-02)') is fully contained within the period of applicability of the sequenced delete statement (PERIOD '(2007-01-01, 2008-03-01)'):

   SEQUENCED VALIDTIME PERIOD '(2007-01-01, 2008-03-01)' DELETE
   FROM Policy;