A current update affects only rows that are valid at the current time. These are rows in valid-time or bitemporal tables with a PV that overlaps current time at the time of the update: Rows that have a valid-time period that contains or begins at TEMPORAL_TIMESTAMP (or TEMPORAL_DATE, depending on the type of the valid-time column).
If additional search conditions are specified in the UPDATE statement, they are applied to these current rows. The search condition may specify conditions on both the valid-time and transaction-time columns.
- Rows in valid-time or bitemporal tables with a PV that ends before or at current time. These rows are history rows in valid time, so do not qualify for a current update.
- Rows in valid-time or bitemporal tables with a PV that begins after current time. These rows are future rows in valid time, so do not qualify for a current update.
- Rows in bitemporal tables with a transaction-time period that ends before UNTIL_CLOSED (before 9999-12-31 23:59:59.999999+00:00). These rows are considered closed in transaction time, and are unavailable to most SQL. They are part of the automatic internal history of changes maintained by the database for tables with transaction time, and do not qualify for a current update.
A current update to a row can result in zero, one, or two additional rows being added to the database, depending on the relationship between the PV of the qualifying row and the current time, and on whether the table is a valid-time or bitemporal table.
Current Updates to Qualifying Rows in Valid-Time Tables
- The valid-time period for the original row, is set to end at the time of the update (TEMPORAL_TIMESTAMP or TEMPORAL_DATE, depending on the type of the valid-time column). No other column values are changed.
This means that the original row is no longer valid. It becomes a history row, showing the original column values that were valid before the update.
- A copy of the original row is inserted which has the new values in the updated columns. The valid-time period of this row is set to begin at the time of the update, and end at the same time as the original row.
- The current update operation updates the row.
The PA of a current update begins at TEMPORAL_TIMESTAMP at the time of the update, and ends at UNTIL_CHANGED, an indefinite time in the future when the row is changed or deleted. Because this PA matches or contains the PV of the qualified row, the change is valid for the entire PV of the row. New rows do not need to be inserted in the database to account for row states that existed before or after the change.
Current updates to Qualifying Rows in Bitemporal Tables
Bitemporal tables include both a valid-time column and a transaction-time column. The results of a current update operation on a bitemporal table with respect to the valid-time column are the same as those for a valid-time table. Due to the transaction-time column, every row that is changed as a result of the current update generates an additional row in the database to track the change in transaction time by creating a snapshot of the row prior to the change:
- The transaction-time period for the original row is set to end at the time of the update (TT_TIMESTAMP), marking the row as closed in the transaction-time dimension. No other values are changed in the row.
This preserves the original row with the original values that existed before the modification, including the original period of validity. Because the row is closed in transaction-time, it becomes inaccessible to further modifications.
- A copy of the original row is inserted. The row has the valid-time period set to end at the time of the update (TEMPORAL_TIMESTAMP or TEMPORAL_DATE, depending on the type of the valid-time column). No other column values are changed.
This means that the original row is no longer valid. It becomes a history row, showing the original column values that were valid before the update.
The value of the transaction-time column is set to (TT_TIMESTAMP, UNTIL_CLOSED), as it is for any new row that is inserted to a table having a transaction-time dimension. The row is therefore open in transaction time, and remains accessible as a history row to valid-time SQL.
- A copy of the original row, which has the new values in the updated columns, is inserted.
The valid-time period of this row is set to begin at the time of the update and end at the same time as the original row.
The value of the transaction-time column is set to (TT_TIMESTAMP, UNTIL_CLOSED), as it is for any new row that is inserted to a table having a transaction-time dimension. The row is therefore open in transaction time, and remains accessible to valid-time SQL.
- The transaction-time period for the original row is set to end at the time of the update (TT_TIMESTAMP), marking the row as closed in the transaction-time dimension. No other values are changed in the row.
This preserves the original row with the original values that existed before the modification, including the original period of validity. Because the row is closed in transaction-time, it becomes inaccessible to further modifications.
- The current update operation updates the row.
A copy of the original row is inserted. The row has the new values in the updated columns. The PA of a current update begins at TEMPORAL_TIMESTAMP at the time of the update, and ends at UNTIL_CHANGED, an indefinite time in the future when the row is changed or deleted. Because this PA matches or contains the PV of the qualified row, the change is valid for the entire PV of the row. New rows do not need to be inserted in the database to account for row states that existed before or after the change.
The value of the transaction-time column is set to (TT_TIMESTAMP, UNTIL_CLOSED), as it is for any new row that is inserted to a table having a transaction-time dimension. The row is therefore open in transaction time, and remains accessible to valid-time SQL.
Usage Notes
- The value of TEMPORAL_TIMESTAMP used to stamp the valid-time column is the same for all rows produced as a result of a single update operation.
- The value of TT_TIMESTAMP used to stamp the transaction-time column is the same for all rows produced as a result of a single update operation.
- If an update to a qualified row does not actually change any column values in a row, temporal operations that close, open, and create new rows are not performed on the row. However, the activity count of the update operation includes these rows, and an update trigger qualifies these rows.
- The modified and inserted rows must not violate any constraints on the table. If there are no uniqueness constraints, inserted rows are not checked for duplicates. If the table has any constraints defined, inserted rows are validated to ensure that the rows do not violate the constraints.