CURRENT DML modifications can cause serializability issues for concurrent transactions. See Potential Concurrency Issues with Current Temporal DML for information on avoiding these issues.
For a table with valid time, current rows 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.
A current DELETE affects only current rows. Future rows with valid-time periods that do not overlap TEMPORAL_TIMESTAMP or TEMPORAL_DATE will not be deleted.
The following table describes the current delete operation of a qualified row.
| IF the table is a … | AND the beginning bound is … | THEN the … |
|---|---|---|
| valid-time table and the element type of the valid-time column is DATE | equal to TEMPORAL_DATE | qualified row is physically deleted. |
| less than TEMPORAL_DATE | period of validity of the qualified row is modified with the ending bound set to TEMPORAL_DATE. | |
| valid-time table and the element type of the valid-time column is TIMESTAMP | equal to TEMPORAL_TIMESTAMP | qualified row is physically deleted. |
| less than TEMPORAL_TIMESTAMP | period of validity of the qualified row is modified with the ending bound set to TEMPORAL_TIMESTAMP. | |
| bitemporal table and the element type of the valid-time column is DATE | equal to TEMPORAL_DATE | qualified row is closed out in transaction time; that is, it is logically deleted. |
| less than TEMPORAL_DATE | qualified row is closed out in transaction time and a copy of the old row is inserted with the beginning bound of the period of validity set to the same value as the closed out row and the ending bound of the period of validity set to TEMPORAL_DATE. | |
| bitemporal table and the element type of the valid-time column is TIMESTAMP | equal to TEMPORAL_TIMESTAMP | qualified row is closed out in transaction time; that is, it is logically deleted. |
| less than TEMPORAL_TIMESTAMP | qualified row is closed out in transaction time and a copy of the old row is inserted with the beginning bound of the period of validity set to the same value as the closed out row and the ending bound of the period of validity set to TEMPORAL_TIMESTAMP. |