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:
  1. The row where the policy_id column value is B345 is deleted from the system-period temporal table.
  2. 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.