A sequenced update modifies the specified rows at each point in time that is covered in the period of applicability. That is, rows whose period of validity overlaps the period of applicability are modified for the overlapping portion.
A sequenced update can modify current, history, or future rows in the valid-time dimension depending on the selection time period (period of applicability) specified in UPDATE statement. For bitemporal tables, which include a transaction-time dimension, only rows that are open in transaction time can qualify for the sequenced update.
If the columns modified in the SET clause do not change the values for a row, the row is not changed with any temporal update semantics. The activity count of the update includes the row and an update trigger qualifies such a row but the modification semantics that close, open, or create new rows are not performed.
Only open rows whose period of validity overlaps the period of applicability of the sequenced update are candidates for the update. Optionally, additional qualifications can be placed on the values of valid-time and transaction-time columns to further filter the rows that will qualify for the update.
A sequenced update of a row in a valid-time table results in the modification of the old row and, potentially, the insertion of new rows, depending on the relationship between the period of validity of the row and the period of applicability of the update.
A sequenced update of a row in a bitemporal table first closes out the old row in transaction time. A copy of the row is made, open in transaction time, and modifications are made appropriate to the update syntax. These modifications can include simply updating the row, or splitting the row into two or three rows, depending on the relationship between the period of validity of the row and the period of applicability specified by the update statement.
The following table describes the sequenced update operation in a table with valid time.
| IF the period of applicability of the update … | THEN … |
|---|---|
| is contained within the period of validity of a row | the row qualifies for the update, but only during the portion of the period of validity that overlaps the period of applicability of the update. Because the row values are updated for only a portion of the original row period of validity, the original row values remain valid before and after the period of applicability of the update. Therefore, the update operation results in three rows:
|
| overlaps the period of validity such that the beginning bound of the period of applicability is between the beginning and ending bounds of the period of validity | the update results in one new row and one old existing row:
|
| overlaps the period of validity such that the ending bound of the period of applicability is between the beginning and ending bounds of the period of validity | the update results in one new row and one old existing row:
|
| contains the period of validity, including the case where the period of applicability equals the period of validity | the existing row is updated for the specified columns. There is no change in the period of validity. |
Temporal constraints that are defined on a table being updated apply to both the existing row that is modified and to the rows that are newly inserted.