Updating data in an application-period temporal table

Updating data in an application-period temporal table can be similar to updating data in a regular table, but data can also be updated for specified points of time in the past, present, or future. Point in time updates can result in rows being split and new rows being inserted automatically into the table.

About this task

In addition to the regular UPDATE statement, application-period temporal 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.

Procedure

To update data in an application-period temporal table, use the UPDATE statement.
For example, you discovered some errors in the insurance coverage information for some customers and the following updates are performed on the sample table that was introduced in the Inserting data into an application-period temporal table topic.
The following table contains the original policy_info table data.
Table 1. Original data in the application-period temporal table (policy_info)
policy_id coverage bus_start bus_end
A123 12000 2008-01-01 2008-07-01
A123 16000 2008-07-01 2009-01-01
B345 18000 2008-01-01 2009-01-01
C567 20000 2008-01-01 2009-01-01
The policy_info table was created with a BUSINESS_TIME WITHOUT OVERLAPS index. When using the regular UPDATE statement, you must ensure that no BUSINESS_TIME periods overlap. Updating an application-period temporal table by using the FOR PORTION OF BUSINESS_TIME clause avoids period overlap problems. This clause causes rows to be changed and can result in rows that are inserted when the existing time period for a row that is being 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) and the coverage should be 18500:
    UPDATE policy_info
  SET coverage = 18500, 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 bus_start column value is updated to 2008-03-01 and the coverage value is updated to 18500. Note that updates to a BUSINESS_TIME period column cannot include the FOR PORTION OF BUSINESS_TIME clause.
    Table 2. Policy B345 updated
    policy_id coverage bus_start bus_end
    A123 12000 2008-01-01 2008-07-01
    A123 16000 2008-07-01 2009-01-01
    B345 18500 2008-03-01 2009-01-01
    C567 20000 2008-01-01 2009-01-01
  • 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 this 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 coverage value is updated to 25000. The bus_start and bus_end column values are unchanged.
    Table 3. Policy C567 updated
    policy_id coverage bus_start bus_end
    A123 12000 2008-01-01 2008-07-01
    A123 16000 2008-07-01 2009-01-01
    B345 18500 2008-03-01 2009-01-01
    C567 25000 2008-01-01 2009-01-01
  • The coverage for policy A123 shows an increase from 12000 to 16000 on July 1 (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 this time period.
    1. 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.
    2. 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 update causes the following things to occur:
    1. 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.
    2. 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.
    Table 4. Policy A123 updated
    policy_id coverage bus_start bus_end
    A123 12000 2008-01-01 2008-06-01
    A123 14000 2008-06-01 2008-07-01
    A123 14000 2008-07-01 2008-08-01
    A123 16000 2008-08-01 2009-01-01
    B345 18500 2008-03-01 2009-01-01
    C567 25000 2008-01-01 2009-01-01

More examples

This section contains more updating application-period temporal table examples.
Merging content
In the following example, a MERGE statement uses the FOR PORTION OF clause to update the policy_info table with the contents of another table (merge_policy).
Table 5. Content of the merge_policy table
policy_id coverage bus_start bus_end
C567 30000 2008-10-01 2010-05-01
H789 16000 2008-10-01 2010-05-01
  1. Create global variables to hold the FROM and TO dates for the FOR PORTION OF clause.
    CREATE VARIABLE sdate DATE default '2008-10-01';
CREATE VARIABLE edate DATE default '2010-05-01';
  2. Issue a MERGE statement that merges the content of merge_policy into the policy_info table that resulted from the updates in the preceding Procedure section. 
    MERGE INTO policy_info pi1
   USING (SELECT policy_id, coverage, bus_start, bus_end
            FROM merge_policy) mp2
   ON (pi1.policy_id = mp2.policy_id)
WHEN MATCHED THEN 
     UPDATE FOR PORTION OF BUSINESS_TIME FROM sdate TO edate
        SET pi1_coverage = mp2.coverage
WHEN NOT MATCHED THEN 
      INSERT (policy_id, coverage, bus_start, bus_end) 
       VALUES (mp2.policy_id, mp2.coverage, mp2.bus_start, mp2.bus_end)
The policy_id C567 is common to both tables. The C567 bus_start value in merge_policy overlaps the C567 bus_end value in policy_info. This statement results in the following items:
  • The bus_end value for coverage of 25000 is set to 2008-10-01.
  • A new row is inserted for coverage of 30000 with the bus_start and bus_end values from merge_policy.
The policy_id H789 exists only in merge_policy and so a new row is added to policy_info.
Table 6. Merged updated data in an application-period temporal table (policy_info)
policy_id coverage bus_start bus_end
A123 12000 2008-01-01 2008-06-01
A123 14000 2008-06-01 2008-07-01
A123 14000 2008-07-01 2008-08-01
A123 16000 2008-08-01 2009-01-01
B345 18000 2008-03-01 2009-01-01
C567 25000 2008-01-01 2008-10-01
C567 30000 2008-10-01 2010-05-01
H789 16000 2008-10-01 2010-05-01
Update targets
The FOR PORTION OF BUSINESS_TIME clause can be used only when the target of the update statement is a table or a view. The following updates return errors.
UPDATE (SELECT * FROM policy_info) FOR PORTION OF BUSINESS_TIME 
   FROM '2008-01-01' TO '06-15-2008' SET policy_id = policy_id + 1;

UPDATE (SELECT * FROM policy_info FOR BUSINESS_TIME AS OF '2008-01-01') 
   FOR PORTION OF BUSINESS_TIME FROM '2008-01-01' TO '06-15-2008'
   SET policy_id = policy_id + 1;
Updating a view
A view with references to an application-period temporal table is updatable. The following UPDATE would update the policy_info table.
CREATE VIEW viewC AS SELECT * FROM policy_info;
UPDATE viewC SET coverage = coverage + 5000;
A view with an application-period temporal table in its FROM clause that contains a period specification is also updatable. This condition differs from views on system-period temporal tables and bitemporal tables. 
CREATE VIEW viewD AS SELECT * FROM policy_info 
   FOR BUSINESS_TIME AS OF CURRENT DATE;
UPDATE viewD SET coverage = coverage - 1000;
A FOR PORTION OF update clause can be included against views with references to application-period temporal tables or bitemporal tables. Such updates are propagated to the temporal tables referenced in the FROM clause of the view definition. 
CREATE VIEW viewE AS SELECT * FROM policy_info;
UPDATE viewE FOR PORTION OF BUSINESS_TIME 
   FROM '2009-01-01' TO '2009-06-01' SET coverage = coverage + 500;