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.