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.