DELETE Statement (ANSI Bitemporal Table Form) | Teradata Vantage - 17.00 - DELETE (ANSI Bitemporal Table Form) - Teradata Database

Teradata Vantage™ - ANSI Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1186-170K

Purpose

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

Syntax

The syntax used for deleting rows in bitemporal tables is the same as the syntax used for valid-time tables, described in DELETE (ANSI Valid-Time Table Form).

Examples of DELETE

Example: Simple DELETE from an ANSI Bitemporal Table

This example uses the following ANSI bitemporal table:

 eid ename deptno terms   job_start    job_end                         sys_start                          sys_end
---- ----- ------ -----  ---------- ----------  -------------------------------- --------------------------------
1002 Ash      333  TA05  2003/01/01 2003/12/31  2003-12-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1005 Alice    222  TW10  2004/12/01 9999/12/31  2004-12-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
1010 Mike     444  TW07  2015/01/01 2016/12/31  2004-12-01 00:12:23.120000-08:00 9999-12-31 23:59:59.999999+00:00
1001 Sania    111  TW08  2002/01/01 2006/12/31  2002-01-01 00:00:00.000000-08:00 2002-07-01 12:00:00.350000+00:00
1004 Fred     222  PW12  2001/05/01 9999/12/31  2001-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00
1003 SRK      111  TM02  2004/02/10 2005/02/10  2004-02-10 00:00:00.000000-08:00 2004-12-01 00:12:23.120000+00:00

A simple SELECT shows the open rows that have not been logically deleted from the table. Only these rows are subject to deletion:

SELECT * FROM employee_bitemp;

 eid ename deptno terms   job_start    job_end                         sys_start                          sys_end
---- ----- ------ -----  ---------- ----------  -------------------------------- --------------------------------
1002 Ash      333  TA05  2003/01/01 2003/12/31  2003-12-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1005 Alice    222  TW10  2004/12/01 9999/12/31  2004-12-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
1010 Mike     444  TW07  2015/01/01 2016/12/31  2004-12-01 00:12:23.120000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred     222  PW12  2001/05/01 9999/12/31  2001-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00

A simple DELETE that has no temporal qualifications logically deletes (closes) the row in system time. Using a nontemporal query, the row no longer appears in the table:

DELETE FROM employee_bitemp WHERE ename='Ash';

SELECT * FROM employee_bitemp;

 eid ename deptno terms   job_start    job_end                         sys_start                          sys_end
---- ----- ------ -----  ---------- ----------  -------------------------------- --------------------------------
1005 Alice    222  TW10  2004/12/01 9999/12/31  2004-12-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
1010 Mike     444  TW07  2015/01/01 2016/12/31  2004-12-01 00:12:23.120000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred     222  PW12  2001/05/01 9999/12/31  2001-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00

Using a temporal query reveals that the row still exists in the table as a closed row. None of the values in the row have been changed except the ending bound of the system time, which indicates the time the row was deleted:

 eid ename deptno terms   job_start    job_end                         sys_start                           sys_end
---- ----- ------ -----  ---------- ----------  -------------------------------- ---------------------------------
1002 Ash      333  TA05  2003/01/01 2003/12/31  2003-12-01 12:11:00.000000-08:00 2014-02-28  19:40:51.250000-08:00
1005 Alice    222  TW10  2004/12/01 9999/12/31  2004-12-01 12:00:00.450000-08:00 9999-12-31  23:59:59.999999+00:00
1010 Mike     444  TW07  2015/01/01 2016/12/31  2004-12-01 00:12:23.120000-08:00 9999-12-31  23:59:59.999999+00:00
1001 Sania    111  TW08  2002/01/01 2006/12/31  2002-01-01 00:00:00.000000-08:00 2002-07-01  12:00:00.350000+00:00
1004 Fred     222  PW12  2001/05/01 9999/12/31  2001-05-01 12:00:00.350000-08:00 9999-12-31  23:59:59.999999+00:00
1003 SRK      111  TM02  2004/02/10 2005/02/10  2004-02-10 00:00:00.000000-08:00 2004-12-01  00:12:23.120000+00:00

Example: Valid-Time DELETE from an ANSI Bitemporal Table

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

 eid ename deptno terms   job_start    job_end                         sys_start                          sys_end
---- ----- ------ -----  ---------- ----------  -------------------------------- --------------------------------1002 Ash      333  TA05  2003/01/01 2003/12/31  2003-12-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1005 Alice    222  TW10  2004/12/01 9999/12/31  2004-12-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
1010 Mike     444  TW07  2015/01/01 2016/12/31  2004-12-01 00:12:23.120000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred     222  PW12  2001/05/01 9999/12/31  2001-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00

Because the PA of the DELETE statement was within the PV of the affected row, two rows result, just as for a deletion on a valid-time table:

SELECT * FROM employee_bitemp;

 eid ename deptno terms   job_start    job_end                         sys_start                          sys_end
---- ----- ------ -----  ---------- ----------  -------------------------------- --------------------------------
1002 Ash      333  TA05  2003/01/01 2003/12/31  2003-12-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1005 Alice    222  TW10  2004/12/01 9999/12/31  2004-12-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
1010 Mike     444  TW07  2015/01/01 2016/12/31  2004-12-01 00:12:23.120000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred     222  PW12  2001/05/01 2009/01/01  2014-02-28 21:06:33.460000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred     222  PW12  2010/01/01 9999/12/31  2014-02-28 21:06:33.460000-08:00 9999-12-31 23:59:59.999999+00:00

In system time these rows are both considered to be new rows added to the table, which is reflected by their sys_start values that show the time that the middle portion of the Fred row valid time was deleted. To see all that occurred in system time to the table as a result of the “partial” deletion, use a temporal query that shows all rows, open and closed:

SELECT * FROM employee_bitemp
FOR SYSTEM_TIME BETWEEN TIMESTAMP'1900-01-01 00:00:00.000000+00:00' AND CURRENT_TIMESTAMP;


 eid ename deptno terms   job_start    job_end                         sys_start                          sys_end
---- ----- ------ -----  ---------- ----------  -------------------------------- --------------------------------
1002 Ash      333  TA05  2003/01/01 2003/12/31  2003-12-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1005 Alice    222  TW10  2004/12/01 9999/12/31  2004-12-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
1010 Mike     444  TW07  2015/01/01 2016/12/31  2004-12-01 00:12:23.120000-08:00 9999-12-31 23:59:59.999999+00:00
1001 Sania    111  TW08  2002/01/01 2006/12/31  2002-01-01 00:00:00.000000-08:00 2002-07-01 12:00:00.350000+00:00
1004 Fred     222  PW12  2001/05/01 9999/12/31  2001-05-01 12:00:00.350000-08:00 2014-02-28 21:06:33.460000-08:00
1004 Fred     222  PW12  2001/05/01 2009/01/01  2014-02-28 21:06:33.460000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred     222  PW12  2010/01/01 9999/12/31  2014-02-28 21:06:33.460000-08:00 9999-12-31 23:59:59.999999+00:00
1003 SRK      111  TM02  2004/02/10 2005/02/10  2004-02-10 00:00:00.000000-08:00 2004-12-01 00:12:23.120000+00:00

Now you can see there are actually three physical Fred rows in the table. The two new rows that were added to account for Fred’s time before and after the deletion, and the original Fred row has been logically deleted from the table, as reflected by the sys_end time that shows the time of the change. The valid-time bounds in the deleted row are those of the original Fred row, before the deletion.