UPDATE Statement (ANSI System-Time Table Form) | Teradata Vantage - 17.00 - UPDATE (ANSI System-Time 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 column values in existing rows of system-time tables.

Syntax

There is no special additional temporal syntax for the UPDATE statement when used on a system-time table. The syntax is identical to that used for nontemporal tables. That syntax is described fully in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

Usage Notes

Although the syntax for a UPDATE statement is identical for system-time and non-temporal tables, the effects are different. When you change a row in a nontemporal table, the original information in the row no longer exists. The situation can be thought of as if the original row had been deleted and replaced with a new row.

System-time tables make this implicit situation explicit, by literally deleting the original row from the table and inserting a new row into the table to reflect the modified information. Because rows are never physically deleted from system-time tables, the original row is closed in system time, and remains inactive in the table as a snapshot of how the table existed before the change.

Updates so not affect closed rows in system-time tables.

The SET clause cannot include the beginning or ending column components of the SYSTEM_TIME derived period column.

Example

For the following examples, assume the queries are issued against the following system-versioned system-time table named employee_systime that contains a mix of open and closed rows:

 eid ename  deptno                        sys_start                          sys_end
---- ------ ------ -------------------------------- --------------------------------
1002 Ash       333 2003-07-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1005 Alice     222 2004-12-01 00:12:23.120000-08:00 2005-05-01 12:00:00.450000-08:00
1004 Fred      222 2002-07-01 12:00:00.350000-08:00 2005-05-01 12:00:00.350000-08:00
1001 Sania     111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1003 SRK       111 2004-02-10 00:00:00.000000-08:00 2006-03-01 00:00:00.000000-08:00
1004 Fred      555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00
1005 Alice     555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00

In a table that has system time, only the open rows are subject to UPDATEs. A simple SELECT with no temporal qualifiers returns the open table rows:

SELECT * FROM employee_systime;

 eid ename  deptno                        sys_start                          sys_end
---- ------ ------ -------------------------------- --------------------------------
1002 Ash       333 2003-07-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1001 Sania     111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred      555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00
1005 Alice     555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00

UPDATE employee_systime SET deptno=888 WHERE ename=’Ash’;

A simple SELECT shows the row has been changed as expected. Note that the start time for the system-time period records the time of the UPDATE, because it is from this time that the modification is known to the database and effective:

 eid ename  deptno                        sys_start                          sys_end
---- ------ ------ -------------------------------- --------------------------------
1002 Ash       888 2014-02-23 22:27:56.540000-08:00 9999-12-31 23:59:59.999999+00:00
1001 Sania     111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred      555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00
1005 Alice     555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00

A temporal select that shows open and closed rows demonstrates that the original row still exists in the table. It has been closed, and the end boundary of the old row system time has been set to the time of the modification, which is the time when the values in that row ceased to apply or be active in the database.

SELECT * FROM employee_systime 
FOR SYSTEM_TIME BETWEEN TIMESTAMP'1900-01-01 22:14:02.820000-08:00' and CURRENT_TIMESTAMP 
WHERE ename=’Ash’;


 eid ename  deptno                        sys_start                          sys_end
---- ------ ------ -------------------------------- --------------------------------
1002 Ash       888 2014-02-23 22:27:56.540000-08:00 9999-12-31 23:59:59.999999+00:00
1002 Ash       333 2003-07-01 12:11:00.000000-08:00 2014-02-23 22:27:56.540000-08:00

In this way, a system-time table maintains a complete history of changes to the rows in the table.