DELETE (ANSI System-Time Form) - Teradata Database

Purpose  

Deletes one or more rows from system-time tables.

Syntax  

There is no special additional temporal syntax for the DELETE statement when used on a system-time table. The syntax is identical to that used for nontemporal tables. That syntax is described fully in SQL Data Manipulation Language.

ANSI Compliance

This is ANSI SQL:2011 compliant.

Usage Notes  

Although the syntax for a DELETE statement is identical for system-time and non-temporal tables, the effects are different, because rows are not physically deleted from system-time tables as a result of a DELETE.

Because rows are not normally physically deleted from system-time tables, the storage required for these tables will not decrease, even when rows are deleted. To physically delete closed rows from system-time tables, drop the system versioning from the table. This automatically physically deletes all closed rows from the table.

For purposes of archiving closed rows prior to deletion, closed rows may be copied to new tables by means of INSERT ... SELECT operations, after which the new tables can be archived.

Example : Deleting Rows from an ANSI System-Time Table

The following example assumes the DELETE operation is applied to the following system-versioned system-time table named employee_systime:

 eid ename  deptno                        sys_start                          sys_end

---- ------ ------ -------------------------------- --------------------------------

1002 Ash       333 2003-07-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00

1005 Alice     222 2004-12-01 00:12:23.120000-08:00 2005-05-01 12:00:00.450000-08:00

1004 Fred      222 2002-07-01 12:00:00.350000-08:00 2005-05-01 12:00:00.350000-08:00

1001 Sania     111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00

1003 SRK       111 2004-02-10 00:00:00.000000-08:00 2006-03-01 00:00:00.000000-08:00

1004 Fred      555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00

1005 Alice     555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00

The closed rows have already been deleted from the table, so are not subject to further deletion. Only the open rows in a system-time table can be deleted. The open rows are returned by a SELECT statement that is not temporally qualified:

SELECT * FROM employee_systime;

 eid ename  deptno                        sys_start                          sys_end

---- ------ ------ -------------------------------- --------------------------------

1002 Ash       333 2003-07-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00

1005 Alice     555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred      555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00
1001 Sania     111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00

DELETE FROM employee_systime WHERE ename=’Sania’;

A simple SELECT shows that the row has been deleted from the table.

SELECT * FROM employee_systime;

 eid ename  deptno                        sys_start                          sys_end

---- ------ ------ -------------------------------- --------------------------------

1002 Ash       333 2003-07-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00

1005 Alice     555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred      555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00

A temporal query that shows all open and closed rows in the table reveals that the Sania row has only been logically deleted, but remains inactive in the table as a closed row.

SELECT * 
FROM employee_systime 
FOR SYSTEM_TIME FROM TIMESTAMP'1900-01-01 00:00:00.000001-08:00' TO 
CURRENT_TIMESTAMP;

 eid ename  deptno                        sys_start                          sys_end

---- ------ ------ -------------------------------- --------------------------------

1002 Ash       333 2003-07-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00

1005 Alice     222 2004-12-01 00:12:23.120000-08:00 2005-05-01 12:00:00.450000-08:00

1004 Fred      222 2002-07-01 12:00:00.350000-08:00 2005-05-01 12:00:00.350000-08:00

1001 Sania     111 2002-01-01 00:00:00.000000-08:00 2014-02-22 22:32:01.540000-08:00

1003 SRK       111 2004-02-10 00:00:00.000000-08:00 2006-03-01 00:00:00.000000-08:00

1004 Fred      555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00

1005 Alice     555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00

In this way, a system-time table maintains a complete history of row deletions in the table.