UPDATE Statement (ANSI Bitemporal Table Form) | Teradata Vantage - 17.00 - UPDATE (ANSI Bitemporal Table Form) - Teradata Database

Teradata Vantage™ - ANSI Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1186-170K

Purpose

Modifies one or more existing rows in ANSI bitemporal tables with the option of modifying the rows for only a portion of their valid-time periods.

Syntax

The syntax used for updating rows in ANSI bitemporal tables is the same as the syntax used for valid-time tables, described in UPDATE (ANSI Valid-Time Table Form).

Example: Updating a Row in an ANSI Bitemporal Table

This example uses the following ANSI bitemporal table:

 eid ename deptno terms   job_start     job_end                         sys_start                           sys_end
---- ----- ------ -----  ----------  ----------  --------------------------------  --------------------------------
1002 Ash      333  TA05  2003/01/01  2003/12/31  2003-12-01 12:11:00.000000-08:00  9999-12-31 23:59:59.999999+00:00
1005 Alice    222  TW10  2004/12/01  9999/12/31  2004-12-01 12:00:00.450000-08:00  9999-12-31 23:59:59.999999+00:00
1010 Mike     444  TW07  2015/01/01  2016/12/31  2004-12-01 00:12:23.120000-08:00  9999-12-31 23:59:59.999999+00:00
1001 Sania    111  TW08  2002/01/01  2006/12/31  2002-01-01 00:00:00.000000-08:00  2002-07-01 12:00:00.350000+00:00
1004 Fred     222  PW12  2001/05/01  9999/12/31  2001-05-01 12:00:00.350000-08:00  9999-12-31 23:59:59.999999+00:00
1003 SRK      111  TM02  2004/02/10  2005/02/10  2004-02-10 00:00:00.000000-08:00  2004-12-01 00:12:23.120000+00:00

Only rows that are still open in system time can be DELETED or UPDATED in an ANSI bitemporal table. To see those rows issue a simple SELECT:

SELECT * FROM employee_bitemp;

 eid ename deptno terms   job_start     job_end                         sys_start                           sys_end
---- ----- ------ -----  ----------  ----------  --------------------------------  --------------------------------
1002 Ash      333  TA05  2003/01/01  2003/12/31  2003-12-01 12:11:00.000000-08:00  9999-12-31 23:59:59.999999+00:00
1005 Alice    222  TW10  2004/12/01  9999/12/31  2004-12-01 12:00:00.450000-08:00  9999-12-31 23:59:59.999999+00:00
1010 Mike     444  TW07  2015/01/01  2016/12/31  2004-12-01 00:12:23.120000-08:00  9999-12-31 23:59:59.999999+00:00
1004 Fred     222  PW12  2001/05/01  9999/12/31  2001-05-01 12:00:00.350000-08:00  9999-12-31 23:59:59.999999+00:00

Now update if a row is updated for a portion of the valid time, two rows will result:

UPDATE employee_bitemp 
FOR PORTION OF job_dur FROM DATE'2005-01-01' TO DATE'9999-12-31' 
SET terms='PW11' 
WHERE ename='Alice';

A simple SELECT shows that where there had been one row for Alice before, now there are two, because the terms of her employment contract changed as of 2005.

 eid ename deptno terms   job_start     job_end                         sys_start                           sys_end
---- ----- ------ -----  ----------  ----------  --------------------------------  --------------------------------
1002 Ash      333  TA05  2003/01/01  2003/12/31  2003-12-01 12:11:00.000000-08:00  9999-12-31 23:59:59.999999+00:00
1005 Alice    222  PW11  2005/01/01  9999/12/31  2014-02-26 00:45:48.450000-08:00  9999-12-31 23:59:59.999999+00:00
1010 Mike     444  TW07  2015/01/01  2016/12/31  2004-12-01 00:12:23.120000-08:00  9999-12-31 23:59:59.999999+00:00
1005 Alice    222  TW10  2004/12/01  2005/01/01  2014-02-26 00:45:48.450000-08:00  9999-12-31 23:59:59.999999+00:00
1004 Fred     222  PW12  2001/05/01  9999/12/31  2001-05-01 12:00:00.350000-08:00  9999-12-31 23:59:59.999999+00:00

Both rows are still open in system time, because the row for Alice was not explicitly deleted, it was only updated. The row with Alice’s information from before the change in terms is considered a history row in valid time. Its valid-time ending bound reflecting the beginning of the PA of the modification, when the old TW10 terms became obsolete. The new Alice row with the new PW11 terms reflects the current terms for Alice. Its valid-time beginning bound reflects the beginning of the PA of the modification, when the new terms became effective.

However, because there was a change to a row, and the table includes a system-time dimension to track every change, the original row for Alice, the row with the original valid-time start and end values, still exists in the table as a closed (logically deleted) row in system time. You can see this if you use a temporal query in system-time to display all open and closed rows in the table:

SELECT * FROM employee_bitemp 
FOR SYSTEM_TIME BETWEEN TIMESTAMP'1900-01-01 22:14:02.820000-08:00' AND CURRENT_TIMESTAMP;

 eid ename deptno terms   job_start     job_end                         sys_start                           sys_end
---- ----- ------ -----  ----------  ----------  --------------------------------  --------------------------------
1002 Ash      333  TA05  2003/01/01  2003/12/31  2003-12-01 12:11:00.000000-08:00  9999-12-31 23:59:59.999999+00:00
1005 Alice    222  PW11  2005/12/01  9999/12/31  2014-02-26 00:45:48.450000-08:00  9999-12-31 23:59:59.999999+00:00
1010 Mike     444  TW07  2015/01/01  2016/12/31  2004-12-01 00:12:23.120000-08:00  9999-12-31 23:59:59.999999+00:00
1005 Alice    222  PW11  2004/12/01  2005/01/31  2014-02-26 00:45:48.450000-08:00  9999-12-31 23:59:59.999999+00:00
1004 Fred     222  PW12  2001/05/01  9999/12/31  2001-05-01 12:00:00.350000-08:00  9999-12-31 23:59:59.999999+00:00
1005 Alice    222  TW10  2004/12/01  9999/12/31  2004-12-01 12:00:00.450000-08:00  2014-02-26 00:45:48.450000-08:00
1003 SRK      111  TM02  2004/02/10  2005/02/10  2004-02-10 00:00:00.000000-08:00  2004-12-01 00:12:23.120000+00:00
1001 Sania    111  TW08  2002/01/01  2006/12/31  2002-01-01 00:00:00.000000-08:00  2002-07-01 12:00:00.350000+00:00

With respect to valid time, the original row was simply modified, and a new row was added to record the new contract terms. The valid-time end of the original row was changed, and the valid-time start of the new row reflects when the new row terms became effective.

With respect to system time, the original row with the original valid-time period was deleted from the table. Because system time and valid time are independent dimensions, a change to the end of the valid-time period in the original row, constitutes a simple row modification that necessitates the original row be closed in system time and logically deleted from the table. So in addition to the two new Alice rows that resulted from the change in the original row in valid time, the original row itself is closed and constitutes a third Alice row, closed in system time, to provide a permanent record of the exact row before the change.

With respect to system time, the beginning and ending bound columns of the valid-time period are just regular data columns, so whenever either of those values changes, the original row as it existed before the change is marked closed in system time but preserved in the table.