Example: Delete from an ANSI Valid-Time Table where PA of Deletion Overlaps PV of Row - Analytics Database - Teradata Vantage

ANSI Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-10-30
dita:mapPath
jqu1628112571823.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esa1472244798285
lifecycle
latest
Product Category
Teradata Vantage™

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

Output:

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

Output:

 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