Deleting data from a bitemporal table

Deleting data from a bitemporal table results in rows that are deleted from the table, rows that are added to its associated history table and can potentially result in new rows that are inserted into the bitemporal table itself.

About this task

In addition to the regular DELETE statement, bitemporal tables also support time range deletes where the DELETE statement includes the FOR PORTION OF BUSINESS_TIME clause. A row is a candidate for deletion if its period-begin column, period-end column, or both falls within the range specified in the FOR PORTION OF BUSINESS_TIME clause. Any existing impacted rows are copied to the history table before they are deleted.

Procedure

To delete data from a bitemporal table, use the DELETE FROM statement.
For example, it was discovered that policy A123 did not have coverage from June 15, 2008 to August 15, 2008. The data was deleted on September 1, 2011 (2011-09-01) from the table that was updated in the Updating data in a bitemporal table topic. 
DELETE FROM policy_info
   FOR PORTION OF BUSINESS_TIME FROM '2008-06-15' TO '2008-08-15'
   WHERE policy_id = 'A123';

Results

The original policy_info table and hist_policy_info table data is as follows:
Table 1. Data in the bitemporal table (policy_info) before the DELETE statement
policy_id coverage bus_start bus_end sys_start sys_end ts_id
A123 12000 2008-01-01 2008-06-01 2011-02-28-
09.10.12.
649592000000		 9999-12-30-
00.00.00.
000000000000		 2011-02-28-
09.10.12.
649592000000
A123 14000 2008-06-01 2008-07-01 2011-02-28-
09.10.12.
649592000000		 9999-12-30-
00.00.00.
000000000000		 2011-02-28-
09.10.12.
649592000000
A123 14000 2008-07-01 2008-08-01 2011-02-28-
09.10.12.
649592000000		 9999-12-30-
00.00.00.
000000000000		 2011-02-28-
09.10.12.
649592000000
A123 16000 2008-08-01 2009-01-01 2011-02-28-
09.10.12.
649592000000		 9999-12-30-
00.00.00.
000000000000		 2011-02-28-
09.10.12.
649592000000
B345 18000 2008-03-01 2009-01-01 2011-02-28-
09.10.12.
649592000000		 9999-12-30-
00.00.00.
000000000000		 2011-02-28-
09.10.12.
649592000000
C567 25000 2008-01-01 2009-01-01 2011-02-28-
09.10.12.
649592000000		 9999-12-30-
00.00.00.
000000000000		 2011-02-28-
09.10.12.
649592000000
Table 2. Data in the history table (hist_policy_info) before the DELETE statement
policy_id coverage bus_start bus_end sys_start sys_end ts_id
A123 12000 2008-01-01 2008-07-01 2010-01-31-
22.31.33.
495925000000		 2011-02-28-
09.10.12.
649592000000		 2010-01-31-
22.31.33.
495925000000
A123 16000 2008-07-01 2009-01-01 2010-01-31-
22.31.33.
495925000000		 2011-02-28-
09.10.12.
649592000000		 2010-01-31-
22.31.33.
495925000000
B345 18000 2008-01-01 2009-01-01 2010-01-31-
22.31.33.
495925000000		 2011-02-28-
09.10.12.
649592000000		 2010-01-31-
22.31.33.
495925000000
C567 20000 2008-01-01 2009-01-01 2010-01-31-
22.31.33.
495925000000		 2011-02-28-
09.10.12.
649592000000		 2010-01-31-
22.31.33.
495925000000
Deleting data from a bitemporal table by using the FOR PORTION OF BUSINESS_TIME clause causes rows to be deleted and can result in rows that are inserted when the time period for a row covers a portion of the range specified in the DELETE FROM statement. Deleting data related to policy A123 applies to the BUSINESS_TIME period from 2008-06-15 to 2008-08-15. There are three rows in the policy_info table for policy_id A123 that include all or part of that time period.
As a result, the following things occur:
  • There is one row where the BUSINESS_TIME period in the DELETE FROM statement covers the entire time period for a row. The row with a bus_start value of 2008-07-01 and a bus_end value of 2008-08-01 is deleted.
  • When only the bus_end value falls into the specified period, the row is deleted. A new row is inserted with the original values from the deleted row, except that the bus_end value is set to 2008-06-15. The sys_start, sys_end, and ts_id column entries are generated by the database manager.
  • When only the bus_start value falls into the specified period, the row is deleted. A new row is inserted with the original values from the deleted row, except that the bus_start value is set to 2008-08-15. The sys_start, sys_end, and ts_id column entries are generated by the database manager.
  • The original rows are moved to the history table. The database manager updates the sys_end value to the date of the delete.
Table 3. Data in the bitemporal table (policy_info) after the DELETE statement
policy_id coverage bus_start bus_end sys_start sys_end ts_id
A123 12000 2008-01-01 2008-06-01 2011-02-28-
09.10.12.
649592000000		 9999-12-30-
00.00.00.
000000000000		 2011-02-28-
09.10.12.
649592000000
A123 14000 2008-06-01 2008-06-15 2011-09-01-
12.18.22.
959254000000		 9999-12-30-
00.00.00.
000000000000		 2011-09-01-
12.18.22.
959254000000
A123 16000 2008-08-15 2009-01-01 2011-09-01-
12.18.22.
959254000000		 9999-12-30-
00.00.00.
000000000000		 2011-09-01-
12.18.22.
959254000000
B345 18000 2008-03-01 2009-01-01 2011-02-28-
09.10.12.
649592000000		 9999-12-30-
00.00.00.
000000000000		 2011-02-28-
09.10.12.
649592000000
C567 25000 2008-01-01 2009-01-01 2011-02-28-
09.10.12.
649592000000		 9999-12-30-
00.00.00.
000000000000		 2011-02-28-
09.10.12.
649592000000
Table 4. History table (hist_policy_info) after DELETE statement
policy_id coverage bus_start bus_end sys_start sys_end ts_id
A123 12000 2008-01-01 2008-07-01 2010-01-31-
22.31.33.
495925000000		 2011-02-28-
09.10.12.
649592000000		 2010-01-31-
22.31.33.
495925000000
A123 16000 2008-07-01 2009-01-01 2010-01-31-
22.31.33.
495925000000		 2011-02-28-
09.10.12.
649592000000		 2010-01-31-
22.31.33.
495925000000
B345 18000 2008-01-01 2009-01-01 2010-01-31-
22.31.33.
495925000000		 2011-02-28-
09.10.12.
649592000000		 2010-01-31-
22.31.33.
495925000000
C567 20000 2008-01-01 2009-01-01 2010-01-31-
22.31.33.
495925000000		 2011-02-28-
09.10.12.
649592000000		 2010-01-31-
22.31.33.
495925000000
A123 14000 2008-06-01 2008-07-01 2011-02-28-
09.10.12.
649592000000		 2011-09-01-
12.18.22.
959254000000		 2011-09-01-
12.18.22.
959254000000
A123 14000 2008-07-01 2008-08-01 2011-02-28-
09.10.12.
649592000000		 2011-09-01-
12.18.22.
959254000000		 2011-09-01-
12.18.22.
959254000000
A123 16000 2008-08-01 2009-01-01 2011-02-28-
09.10.12
.649592000000		 2011-09-01-
12.18.22.
959254000000		 2011-09-01-
12.18.22.
959254000000