Deleting data from a system-period temporal table

Deleting data from a system-period temporal table removes rows from the table and adds rows to the associated history table. The rows are added with the appropriate system timestamps.

About this task

In addition to deleting the specified rows of the system-period temporal table, the DELETE FROM statement moves a copy of the existing row into the associated history table before the row is deleted from the system-period temporal table.

Procedure

To delete data from a system-period temporal table, use the DELETE FROM statement.

For example, the owner of policy B345 decides to cancel insurance coverage. The data was deleted on September 1, 2011 (2011-09-01) from the table that was updated in the Updating data in a system-period temporal table topic.

DELETE FROM policy_info WHERE policy_id = 'B345';

Results

The original policy_info table data is as follows:

Table 1. Data in the system-period temporal table (policy_info) before the DELETE statement
policy_id	coverage	sys_start	sys_end	ts_id
A123	12000	2010-01-31-22.31.33.495925000000	9999-12-30-00.00.00.000000000000	2010-01-31-22.31.33.495925000000
B345	18000	2010-01-31-22.31.33.495925000000	9999-12-30-00.00.00.000000000000	2010-01-31-22.31.33.495925000000
C567	25000	2011-02-28-09.10.12.649592000000	9999-12-30-00.00.00.000000000000	2011-02-28-09.10.12.649592000000

The deletion of policy B345 affects the system-period temporal table and its history table, causing the following things to occur:

The row where the policy_id column value is B345 is deleted from the system-period temporal table.
The original row is moved to the history table. The database manager updates the sys_end column value to the date of the delete.

Table 2. Data in the system-period temporal table (policy_info) after the DELETE statement
policy_id	coverage	sys_start	sys_end	ts_id
A123	12000	2010-01-31-22.31.33.495925000000	9999-12-30-00.00.00.000000000000	2010-01-31-22.31.33.495925000000
C567	25000	2011-02-28-09.10.12.649592000000	9999-12-30-00.00.00.000000000000	2011-02-28-09.10.12.649592000000

Table 3. History table (hist_policy_info) after delete
policy_id	coverage	sys_start	sys_end	ts_id
C567	20000	2010-01-31-22.31.33.495925000000	2011-02-28-09.10.12.649592000000	2010-01-31-22.31.33.495925000000
B345	18000	2010-01-31-22.31.33.495925000000	2011-09-01-12.18.22.959254000000	2010-01-31-22.31.33.495925000000

Example

This section contains more examples of delete operations on system-period temporal tables.

Time specifications

In the following example, a time period is specified as part of the DELETE statement. The following delete is run after the delete in the preceding Procedure section.

DELETE FROM (SELECT * FROM policy_info
   FOR SYSTEM_TIME AS OF '2010-01-31-22.31.33.495925000000')
   WHERE policy_id = C567;

This DELETE statement returns an error. The SELECT statement explicitly queries the policy_info table and implicitly queries its associated history table (hist_policy_info). The row with a policy_id column value of C567 in the hist_policy_info table would be returned by the SELECT statement, but rows in a history table that were accessed implicitly cannot be deleted.