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
statementpolicy_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 statementpolicy_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 deletepolicy_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.