Example: UPDATE on an ANSI Valid-Time Table Where PA of Update 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™

If the PV of the update lies within the PA of a row, only the portion of the row information that is valid during the overlap is updated, while the portions of row that existed before and after the change remain in the table as separate rows. Assume that Fred has to reduce his work for the company for the year of 2005, and agrees to a change in the terms of his contract for that period:

UPDATE employee_vt
FOR PORTION OF job_dur FROM DATE'2005-01-01' TO DATE'2006-01-01'
SET terms='TW10'
WHERE ename='Fred';

SELECT * FROM employee_vt WHERE ename=’Fred’;

Output:

 eid ename term    job_start     job_end
---- ----- ----- ----------- -----------
1004 Fred  TW10   2005/01/01  2006/01/01
1004 Fred  PW12   2001/05/01  2005/01/01
1004 Fred  PW12   2006/01/01  9999/12/31

Where there had been one row for Fred in the table before the UPDATE, after the UPDATE there are three rows. The three rows track the changing terms of Fred’s employment, starting with terms PW12, changing to TW10 for one year beginning in 2005, then returning to PW12 in 2006.

Because this was a valid-time UPDATE executed against a valid-time table, all the work of creating new rows and adjusting the valid-times for each row was handled automatically by the database.