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

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

 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.