Updating data in a bitemporal table results in rows that
are added to its associated history table and can potentially result
in rows that are added to the bitemporal table itself.
About this task
In addition to the regular UPDATE statement, bitemporal tables
also support time range updates where the UPDATE statement includes
the FOR PORTION OF BUSINESS_TIME clause. A row is a candidate for
updating if its period-begin column, period-end column, or both fall
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 updated.
Procedure
To update data in a bitemporal table, use the UPDATE
statement to change data rows. For example, it was discovered that
there are some errors in the insurance coverage levels for two customers
and the following data was updated on February 28, 2011 (2011-02-28)
in the example table that had data added in the Inserting data into a bitemporal table
topic.
The
following table is the original
policy_info table
data.
Table 1. Original
data in the bitemporal table (policy_info)
| 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 |
9999-12-30-
00.00.00.
000000000000 |
2010-01-31-
22.31.33.
495925000000 |
| A123 |
16000 |
2008-07-01 |
2009-01-01 |
2010-01-31-
22.31.33.
495925000000 |
9999-12-30-
00.00.00.
000000000000 |
2010-01-31-
22.31.33.
495925000000 |
| B345 |
18000 |
2008-01-01 |
2009-01-01 |
2010-01-31-
22.31.33.
495925000000 |
9999-12-30-
00.00.00.
000000000000 |
2010-01-31-
22.31.33.
495925000000 |
| C567 |
20000 |
2008-01-01 |
2009-01-01 |
2010-01-31-
22.31.33.
495925000000 |
9999-12-30-
00.00.00.
000000000000 |
2010-01-31-
22.31.33.
495925000000 |
Updating a bitemporal table by using the FOR PORTION OF BUSINESS_TIME
clause causes rows to be changed and can result in rows that are inserted
when the existing time period for rows that are updated is not fully
contained within the range specified in the UPDATE statement.
- The coverage for policy B345 actually started on March
1, 2008 (2008-03-01):
UPDATE policy_info
SET bus_start='2008-03-01'
WHERE policy_id = 'B345'
AND coverage = 18000;
The update to policy B345 uses
a regular UPDATE statement. There is only one row in the
policy_info table
for
policy_id B345, so there are no potential BUSINESS_TIME
periods overlaps. As a result the following things occur:
- The
bus_start column value is updated to 2008-03-01.
Note that updates to a BUSINESS_TIME period column cannot include
the FOR PORTION OF BUSINESS_TIME clause.
- The database manager updates the
sys_start and ts_id values
to the date of the update.
- The original row is moved to the history table. The database manager
updates the
sys_end value to the date of the update.
The following tables show the update for policy B345.
Table 2. Bitemporal table (policy_info)
after policy B345 update
| 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 |
9999-12-30-
00.00.00.
000000000000 |
2010-01-31-
22.31.33.
495925000000 |
| A123 |
16000 |
2008-07-01 |
2009-01-01 |
2010-01-31-
22.31.33.
495925000000 |
9999-12-30-
00.00.00.
000000000000 |
2010-01-31-
22.31.33.
495925000000 |
| 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 |
20000 |
2008-01-01 |
2009-01-01 |
2010-01-31-
22.31.33.
495925000000 |
9999-12-30-
00.00.00.
000000000000 |
2010-01-31-
22.31.33.
495925000000 |
Table 3. History table (hist_policy_info)
after policy B345 update
| policy_id |
coverage |
bus_start |
bus_end |
sys_start |
sys_end |
ts_id |
| 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 |
- The coverage for policy C567 should be 25000 for the year
2008:
UPDATE policy_info
FOR PORTION OF BUSINESS_TIME FROM '2008-01-01' TO '2009-01-01'
SET coverage = 25000
WHERE policy_id = 'C567';
The update to policy C567 applies to the BUSINESS_TIME
period from 2008-01-01 to 2009-01-01. There is only one row in the
policy_info table
for
policy_id C567 that includes that time period.
The BUSINESS_TIME period is fully contained within the
bus_start and
bus_end column
values for that row. As a result the following things occur:
- The coverage value for the row with
policy_id C567
is updated to 25000.
- The
bus_start and bus_end column
values are unchanged.
- The database manager updates the
sys_start and ts_id values
to the date of the update.
- The original row is moved to the history table. The database manager
updates the
sys_end value to the date of the update.
The following tables show the update for policy C567.
Table 4. Bitemporal table (policy_info)
after policy C567 update
| 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 |
9999-12-30-
00.00.00.
000000000000 |
2010-01-31-
22.31.33.
495925000000 |
| A123 |
16000 |
2008-07-01 |
2009-01-01 |
2010-01-31-
22.31.33.
495925000000 |
9999-12-30-
00.00.00.
000000000000 |
2010-01-31-
22.31.33.
495925000000 |
| 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 5. History table (hist_policy_info)
after policy C567 update
| policy_id |
coverage |
bus_start |
bus_end |
sys_start |
sys_end |
ts_id |
| 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 |
- The coverage for policy A123 shows an increase from 12000
to 16000 on July 7 (2008-07-01), but an earlier increase to 14000
is missing:
UPDATE policy_info
FOR PORTION OF BUSINESS_TIME FROM '2008-06-01' TO '2008-08-01'
SET coverage = 14000
WHERE policy_id = 'A123';
The update to policy A123
applies to the BUSINESS_TIME period from 2008-06-01 to 2008-08-01.
There are two rows in the
policy_info table for
policy_id A123
that include part of the update time period.
- The BUSINESS_TIME period is partially contained in the row that
has a
bus_start value of 2008-01-01 and a bus_end value
of 2008-07-01. This row overlaps the beginning of the specified period
because the earliest time value in the BUSINESS_TIME period is greater
than the rows bus_start value, but less than its bus_end value.
- The BUSINESS_TIME period is partially contained in the row that
has a
bus_start value of 2008-07-01 and a bus_end value
of 2009-01-01. This row overlaps the end of the specified period because
the latest time value in the BUSINESS_TIME period is greater than
the rows bus_start value, but less than its bus_end value.
As a result the following things occur:
- When the
bus_end value overlaps the beginning
of the specified period, the row is updated to the new coverage value
of 14000. In this updated row, the bus_start value
is set to 2008-06-01 which is the begin value of the UPDATE specified
period, and the bus_end value is unchanged. An additional
row is inserted with the original values from the row, except that
the bus_end value is set to 2008-06-01. This new
row reflects the BUSINESS_TIME period when coverage was 12000. The sys_start, sys_end,
and ts_id column entries are generated by the database
manager.
- When the
bus_start value overlaps the end of
the specified period, the row is updated to the new coverage value
of 14000. In this updated row, the bus_start value
is unchanged and the bus_end value is set to 2008-08-01
which is the end value of the UPDATE specified period. An additional
row is inserted with the original values from the row, except that
the bus_start value is set to 2008-08-01. This new
row reflects the BUSINESS_TIME period when coverage was 16000. 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 update.
The following tables show the update for policy A123.
Table 6. Bitemporal table (policy_info)
after policy A123 update
| 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 7. History table (hist_policy_info)
after policy A123 update
| 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 |