For a table with valid time, rows that overlap with the period of applicability qualify for deletion. Any additional search conditions are applied only on these rows. The conditions in the WHERE clause or join ON conditions can be specified on valid-time or transaction-time columns.
Sequenced delete on a table with transaction time operates only on open rows.
The following table describes the sequenced delete operation of a qualified row.
| IF the table is a … | AND the period of applicability … | THEN … |
|---|---|---|
| valid-time table | contains the period of validity of the qualified row, including the case where they are equal | the qualified row is physically deleted. |
| does not contain the period of validity of the qualified row | If a portion of the period of validity exists before the beginning of the period of applicability, a copy of the row is inserted with the beginning bound of its period of validity set to the same value as the qualified row and the ending bound set to the beginning bound of the period of applicability. If a portion of the period of validity exists after the end of the period of applicability, a copy of the row is inserted with the ending bound of its period of validity set to the same value as the qualified row and the beginning bound set to the ending bound of the period of applicability. Note that two rows can be inserted if the preceding conditions are both true. |
|
| bitemporal table | contains the period of validity of the qualified row, including the case where they are equal | the qualified row is closed out (logically deleted) in transaction time. |
| does not contain the period of validity of the qualified row | the qualified row is closed out in transaction time. If a portion of the period of validity exists before the beginning of the period of applicability, a copy of the row is inserted with the beginning bound of its period of validity set to the same value as the qualified row and the ending bound set to the beginning bound of the period of applicability. If a portion of the period of validity exists after the end of the period of applicability, a copy of the row is inserted with the ending bound of its period of validity set to the same value as the qualified row and the beginning bound set to the ending bound of the period of applicability. Note that two rows can be inserted if the preceding conditions are both true. |