Updating data in a system-period temporal table

Updating data in a system-period temporal table results in rows added to the system-period temporal table's associated history table.

In addition to updating the values in rows of the system-period temporal table, the UPDATE statement causes the database manager to insert a copy of the existing row into the associated history table. The history row is generated as part of the same transaction that updates the row. If a single transaction updates the same row multiple times, only one history row is generated and that row reflects the state of the record before any changes were made by the transaction.

In the following example, a customer's insurance coverage is increased on February 28, 2016 (2016-02-28). This example uses the table created in Creating a system-period temporal table with rows that were added in Inserting data into a system-period temporal table.

The following table contains the POLICY_INFO table data before the update.

Table 1. Data in the system-period temporal table, POLICY_INFO, before the UPDATE statement
POLICY_ID COVERAGE SYS_START SYS_END TS_ID
A123 12000 2015-01-31-22.31.33.495925000000 9999-12-30-00.00.00.000000000000 2015-01-31-22.31.33.495925000000
B345 18000 2015-01-31-22.31.33.495925000000 9999-12-30-00.00.00.000000000000 2015-01-31-22.31.33.495925000000
C567 20000 2015-01-31-22.31.33.495925000000 9999-12-30-00.00.00.000000000000 2015-01-31-22.31.33.495925000000

Update the coverage for policy C567 to 25000.

UPDATE policy_info 
  SET coverage = 25000
  WHERE policy_id = 'C567';
The update to policy C567 affects the system-period temporal table and its history table, causing the following things to occur:
  1. The coverage value for the row with policy C567 is updated to 25000.
  2. In the system-period temporal table, the database manager updates the SYS_START and TS_ID values to the timestamp of the update and the SYS_END value is unchanged.
  3. The original row is inserted into the history table. The database manager updates the SYS_END value to the timestamp of the update. This row can be interpreted as the valid coverage for policy C567 from 2015-01-31-22.31.33.495925000000 to 2016-02-28-09.10.12.649592000000.
Table 2. Data in the system-period temporal table, POLICY_INFO, after the UPDATE statement
POLICY_ID COVERAGE SYS_START SYS_END TS_ID
A123 12000 2015-01-31-22.31.33.495925000000 9999-12-30-00.00.00.000000000000 2015-01-31-22.31.33.495925000000
B345 18000 2015-01-31-22.31.33.495925000000 9999-12-30-00.00.00.000000000000 2015-01-31-22.31.33.495925000000
C567 25000 2016-02-28-09.10.12.649592000000 9999-12-30-00.00.00.000000000000 2016-02-28-09.10.12.649592000000
Table 3. Data in the history table, HIST_POLICY_INFO, after the UPDATE statement
POLICY_ID COVERAGE SYS_START SYS_END TS_ID
C567 20000 2015-01-31-22.31.33.495925000000 2016-02-28-09.10.12.649592000000 2015-01-31-22.31.33.495925000000

If one or more updates occur while running under commitment control, and the user rolls back the transaction, then both the updates to the system-period temporal table and the inserts into the history table are rolled back.

When multiple transactions are updating the same row, timestamp conflicts can occur. When these conflicts occur, the setting for the SYSTIME_PERIOD_ADJ QAQQINI option determines whether timestamp adjustments are made or if transaction fails. The System-period temporal table timestamp value conflicts topic provides more details on timestamp adjustments. Application programmers might consider using SQLCODE or SQLSTATE values to handle potential timestamp value adjustment-related return codes from SQL update statements.