17.10 - Example: Delete from an ANSI Valid-Time Table where PA of Deletion Overlaps PV of Row - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - ANSI Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1186-171K
Language
English (United States)

If the PV of the deletion overlaps the PA of a row, only the portion of the row information that is valid during the overlap is deleted, effectively changing the valid-time period for the row:

DELETE FROM employee_vt
FOR PORTION OF job_dur FROM DATE’2000-01-01’ TO DATE’2002-01-01’
WHERE ename=’Fred’;

SELECT * FROM employee_vt WHERE ename=’Fred’;

 eid ename terms  job_start     job_end
---- ----- ----- ---------- -----------
1004 Fred   PW12 2002/02/01  2009/01/01
1004 Fred   PW12 2010/01/01  9999/12/31


DELETE FROM employee_vt 
FOR PORTION OF job_dur FROM DATE’2008-05-05’ TO DATE’2009-05-05’ 
WHERE ename=’Fred’;

SELECT * FROM employee_vt WHERE ename=’Fred’;
 eid ename terms   job_start     job_end
---- ----- -----  ---------- -----------
1004 Fred   PW12  2002/01/01  2008/05/05
1004 Fred   PW12  2009/05/01  9999/12/31