Example: Simple DELETE from an ANSI Bitemporal Table - Advanced SQL Engine - Teradata Database

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