Example: Delete from an ANSI Valid-Time Table Where PA of Deletion is Within 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™

Temporal tables allow you to “delete” rows for only a portion of their period of validity. The database takes care of adding rows and adjusting valid-time periods to account for the change automatically. The database automatically handles the valid-time modifications for the row, which may involve changing the period bounds and adding new rows to the table.

For example, assume the company grants Fred a year off from his job in 2009. Deleting the Fred row for only that portion of the row period of validity automatically yields two rows for Fred in the table:

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

SELECT * FROM employee_vt WHERE ename=’Fred’;

Output:

 eid ename terms  job_start     job_end
---- ----- ----- ---------- -----------
1004 Fred   PW12 2001/05/01  2009/01/01
1004 Fred   PW12 2010/01/01  9999/12/31
Even though this was a DELETE operation, the net effect was to add a row to the table.