DELETE Statement (ANSI Valid-Time Table Form) | Teradata Vantage - DELETE (ANSI Valid-Time Table Form) - Advanced SQL Engine

Purpose

Deletes one or more rows from valid-time tables with the option of deleting the rows for only a portion of their valid-time periods.

Syntax

DELETE FROM valid_time_table
  [ FOR PORTION OF valid_time_period_name
      FROM point_in_time_1 TO point_in_time_2
  ]
  [ WHERE search_condition ] [;]

valid_time_table

The valid-time table from which you are deleting rows.

FOR PORTION OF

Qualifies rows for deletion that have valid-time periods that are within or that overlap the period defined by point_in_time_1 and point_in_time_2.

valid_time_period_name

The name of the valid-time derived period column. This is not “VALIDTIME.” It is the name assigned to the derived period column when the table was defined.

point_in_time_1

point_in_time_2

Delimits the period of applicability of the deletion.

A date or timestamp expression that can be a constant, scalar UDF, scalar subquery, or business calendar function that evaluates to a DATE or TIMESTAMP[(n)] [WITH TIME ZONE] value.

The expression can be any expression, including parameterized values and built-in functions such as CURRENT_DATE, CURRENT_TIMESTAMP, TEMPORAL_DATE, or TEMPORAL TIMESTAMP. The expression cannot reference any columns, but it can be a self-contained noncorrelated scalar subquery.

WHERE search_condition

For information on the this syntax and other nontemporal keywords, clauses, and options for DELETE, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

ANSI Compliance

This statement is ANSI SQL:2011 compliant.

Usage Notes

You cannot use the FOR PORTION OF qualifier within a MERGE INTO statement.

Examples of Deleting Rows from Valid-Time Tables

For the following examples, assume the queries are issued against the following valid-time table named employee_vt that contains a mix of current, future, and history rows:

 eid ename terms   job_start     job_end
---- ----- ----- ----------- -----------
1002 Ash    TA05  2003/01/01  2003/12/31
1005 Alice  TW11  2004/12/01  2005/12/01
1010 Mike   TW07  2015/01/01  2016/12/31
1005 Alice  PW11  2005/12/01  9999/12/31
1001 Sania  TW08  2002/01/01  2006/12/31
1004 Fred   PW12  2001/05/01  9999/12/31
1003 SRK    TM02  2004/02/10  2005/02/09

Example: Simple DELETE on an ANSI Valid-Time Table

A simple DELETE statement without temporal qualification operates just as a DELETE on a nontemporal table, completely deleting rows that qualify for deletion:

DELETE FROM employee_vt WHERE ename=’Ash’;

SELECT * FROM employee_vt;
 eid ename terms   job_start     job_end
---- ----- ----- ----------- -----------
1005 Alice  TW11  2004/12/01  2005/12/01
1010 Mike   TW07  2015/01/01  2016/12/31
1005 Alice  PW11  2005/12/01  9999/12/31
1001 Sania  TW08  2002/01/01  2006/12/31
1004 Fred   PW12  2001/05/01  9999/12/31
1003 SRK    TM02  2004/02/10  2005/02/09

Example: Delete from an ANSI Valid-Time Table Where PA of Deletion is Within PV of Row

Temporal tables allow you to “delete” rows for only a portion of their period of validity. The database takes care of adding rows and adjusting valid-time periods to account for the change automatically. The database automatically handles the valid-time modifications for the row, which may involve changing the period bounds and adding new rows to the table.

For example, assume the company grants Fred a year off from his job in 2009. Deleting the Fred row for only that portion of the row period of validity automatically yields two rows for Fred in the table:

DELETE FROM employee_vt
FOR PORTION OF job_dur FROM DATE’2009-01-01’ TO DATE’2010-01-01’
WHERE ename=’Fred’;

SELECT * FROM employee_vt WHERE ename=’Fred’;

 eid ename terms  job_start     job_end
---- ----- ----- ---------- -----------
1004 Fred   PW12 2001/05/01  2009/01/01
1004 Fred   PW12 2010/01/01  9999/12/31

Even though this was a DELETE operation, the net effect was to add a row to the table.

Example: Delete from an ANSI Valid-Time Table where PA of Deletion Overlaps PV of Row

If the PV of the deletion overlaps the PA of a row, only the portion of the row information that is valid during the overlap is deleted, effectively changing the valid-time period for the row:

DELETE FROM employee_vt
FOR PORTION OF job_dur FROM DATE’2000-01-01’ TO DATE’2002-01-01’
WHERE ename=’Fred’;

SELECT * FROM employee_vt WHERE ename=’Fred’;

 eid ename terms  job_start     job_end
---- ----- ----- ---------- -----------
1004 Fred   PW12 2002/02/01  2009/01/01
1004 Fred   PW12 2010/01/01  9999/12/31


DELETE FROM employee_vt 
FOR PORTION OF job_dur FROM DATE’2008-05-05’ TO DATE’2009-05-05’ 
WHERE ename=’Fred’;

SELECT * FROM employee_vt WHERE ename=’Fred’;
 eid ename terms   job_start     job_end
---- ----- -----  ---------- -----------
1004 Fred   PW12  2002/01/01  2008/05/05
1004 Fred   PW12  2009/05/01  9999/12/31

DELETE Statement (ANSI Valid-Time Table Form) | Teradata Vantage - DELETE (ANSI Valid-Time Table Form) - Advanced SQL Engine - Teradata Database

ANSI Temporal Table Support