Purpose
Deletes one or more rows from ANSI bitemporal tables with the option of deleting the rows for only a portion of their valid-time periods.
Syntax
The syntax used for deleting rows in bitemporal tables is the same as the syntax used for valid-time tables, described in DELETE (ANSI Valid-Time Table Form).
Examples of DELETE
Example: Simple DELETE from 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
A simple SELECT shows the open rows that have not been logically deleted from the table. Only these rows are subject to deletion:
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
A simple DELETE that has no temporal qualifications logically deletes (closes) the row in system time. Using a nontemporal query, the row no longer appears in the table:
DELETE FROM employee_bitemp WHERE ename='Ash'; SELECT * FROM employee_bitemp; eid ename deptno terms job_start job_end sys_start sys_end ---- ----- ------ ----- ---------- ---------- -------------------------------- -------------------------------- 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
Using a temporal query reveals that the row still exists in the table as a closed row. None of the values in the row have been changed except the ending bound of the system time, which indicates the time the row was deleted:
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 2014-02-28 19:40:51.250000-08: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
Example: Valid-Time DELETE from an ANSI Bitemporal Table
DELETE FROM employee_bitemp FOR PORTION OF job_dur FROM DATE’2009-01-01’ TO DATE’2010-01-01’ WHERE ename=’Fred’; 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
Because the PA of the DELETE statement was within the PV of the affected row, two rows result, just as for a deletion on a valid-time table:
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 2009/01/01 2014-02-28 21:06:33.460000-08:00 9999-12-31 23:59:59.999999+00:00 1004 Fred 222 PW12 2010/01/01 9999/12/31 2014-02-28 21:06:33.460000-08:00 9999-12-31 23:59:59.999999+00:00
In system time these rows are both considered to be new rows added to the table, which is reflected by their sys_start values that show the time that the middle portion of the Fred row valid time was deleted. To see all that occurred in system time to the table as a result of the “partial” deletion, use a temporal query that shows all rows, open and closed:
SELECT * FROM employee_bitemp
FOR SYSTEM_TIME BETWEEN TIMESTAMP'1900-01-01 00:00:00.000000+00: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 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 2014-02-28 21:06:33.460000-08:00
1004 Fred 222 PW12 2001/05/01 2009/01/01 2014-02-28 21:06:33.460000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred 222 PW12 2010/01/01 9999/12/31 2014-02-28 21:06:33.460000-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
Now you can see there are actually three physical Fred rows in the table. The two new rows that were added to account for Fred’s time before and after the deletion, and the original Fred row has been logically deleted from the table, as reflected by the sys_end time that shows the time of the change. The valid-time bounds in the deleted row are those of the original Fred row, before the deletion.