DELETE
Purpose
There are four forms of the DELETE statement.
Use this form when the request specifies a condition that directly references more than one table.
Syntax - Basic Form
Syntax - Join Condition Form
where:
Syntax Element … |
Specifies … |
delete_table_name |
the table or queue table from which the DELETE statement is to remove rows. The delete_table_name specification is optional when you specify a FROM clause. If you do not specify a delete_table_name, then the system deletes only the first table specified in the FROM clause. If you specify a correlation name in the FROM clause, then you must specify that correlation name in place of delete_table_name. |
table_name |
the name of the table, queue table, or view on delete_table_name from which the DELETE operation is to remove rows. |
FROM table_name |
the name of a derived table, joined table, or view in the subquery referenced by the predicate of the WHERE clause. If a row from delete_table is joined with a row from another table in the FROM clause, and the specified WHERE condition for the request evaluates to TRUE for that joined row, then the row in delete_table is deleted; else it is not. See “Rules for Using Scalar Subqueries in a DELETE Statement” on page 353 for the rules for using a scalar subquery for a derived table. If you do not specify a FROM clause, then you cannot use correlation names (compare “Example 8: Join Condition DELETE With FROM Clause and Correlation Name” on page 359 and “Example 9: Join Condition DELETE With No FROM Clause” on page 359). You should also specify the names of all outer tables, including the table from which rows are to be deleted. |
AS correlation_name |
an optional table alias name. You must specify a correlation name for each table specified in a self-join. ANSI calls table aliases correlation names. They are also referred to as range variables. |
condition |
a predicate to filter the list of rows to be deleted. The expression operands can be either constants or references to fields in the specified table or other tables. The predicate also can specify a scalar subquery. See “Scalar Subqueries” on page 142 and “Rules for Using Scalar Subqueries in a DELETE Statement” on page 353 for details. |
ALL |
that all rows in the table are to be deleted. This is the default and is used when a WHERE condition is not specified. The ALL option is a non-ANSI Teradata extension. |
ANSI Compliance
The basic form of DELETE is ANSI SQL:2011-compliant with extensions.
The join condition form of DELETE is a Teradata extension to the ANSI SQL:2011 standard.
Required Privileges
You must have the DELETE privilege on the table.
If the DELETE statement specifies WHERE conditions, you must also have the SELECT privilege on all tables and views through which they are referenced.
Use caution when granting the privilege to delete data through a view. Data in fields that might not be visible to the user is also deleted when a row is deleted through a view.
Recommendation
The basic form of DELETE is ANSI-compliant and you should use it for all delete statements that reference either a single table or reference tables other than the one deleted from only by subqueries in the condition.
Locks and Concurrency
A DELETE statement sets a WRITE lock for the subject table or row hash.
The lock set for a SELECT subquery depends on the isolation level for the session, the setting of the AccessLockForUncomRead DBS Control flag, and whether the subquery is embedded within a SELECT or DELETE statement.
IF the transaction isolation level is … |
AND the DBS Control AccessLockForUncomRead flag is set … |
THEN the default locking severity for outer SELECT and ordinary SELECT subquery operations is … |
AND the default locking severity for SELECT operations embedded within an UPDATE request is … |
SERIALIZABLE |
FALSE |
READ |
READ |
TRUE |
READ |
||
READ UNCOMMITTED
|
FALSE |
READ |
|
TRUE |
ACCESS |
For More Information
DELETE Processing Time
Processing time can vary between different syntaxes used to perform the identical delete operation. Use the EXPLAIN request modifier to determine which syntax form produces optimal processing time.
Unconstrained Fastpath Delete Processing
To ensure that fastpath delete processing is enabled for unconstrained deletes performed using unconditional DELETE or DELETE ... ALL statements, there must not be any restrictions or conditions on the deletions and the statements must be positioned properly.
Restrictions on Deletions
To enable fastpath delete processing:
Positioning Requirements
To enable fastpath delete processing, statements used to perform unconstrained deletes must be positioned properly. Proper positioning is determined by:
Position Within the Transaction
The speed of processing of an unconstrained delete depends on its position within the transaction.
When submitted as the last request of a transaction, other than for an implicit transaction, the DELETE or DELETE ... ALL statement must be the last statement that references the target table in the request that contains an END TRANSACTION or COMMIT. When a request is submitted as an implicit transaction, the DELETE or DELETE ... ALL statement must be the last statement that references the target table in the request.
Proper Positioning For the Session Mode and Transaction Type
Because fastpath delete processing defers the delete until a transaction ends and because it allows rollback and delete processing to be almost instantaneous, make sure that the DELETE or DELETE ... ALL statements are positioned properly based on the active session mode and the transaction type.
Session mode |
Transaction Type |
Positioning Requirements |
ANSI |
ANSI |
The DELETE … ALL or unconditional DELETE must be in the same multistatement request that contains a COMMIT. For example, DELETE FROM table_name ALL
;COMMIT;
|
Teradata |
Implicit (such as a multistatement macro or BTEQ request) |
The DELETE … ALL or unconditional DELETE must be the last statement that references the target table in the request. |
Explicit |
The DELETE … ALL or unconditional DELETE must be in the same multistatement request that contains the END TRANSACTION that terminates the currently open transaction. For example, BEGIN TRANSACTION;
DELETE FROM table_name ALL
;END TRANSACTION;
This is not valid for embedded SQL applications because the DELETE and END TRANSACTION statements must fall into the same request, and embedded SQL does not support multistatement requests. |
Fastpath Delete and Join Indexes
Teradata Database uses fastpath delete processing when the target table has a simple or aggregate join index that is one of the following:
This includes the following statements:
Fastpath Delete for Multiple DELETE Statements
Teradata Database uses fastpath delete processing for multiple DELETE ALL statements in an implicit transaction or in the last request of a transaction.
Constrained (Slow Path) DELETE Processing
The processing time for a constrained delete (DELETE...WHERE) can increase when:
Processing time for a constrained delete can be shortened by specifying a primary index value, a USI value, or a highly selective NUSI value as the conditional expression of the WHERE clause.
Duplicate Rows and DELETE Processing
Duplicate rows in a MULTISET table cannot be distinguished. When a WHERE condition identifies duplicate rows, all duplicate rows are deleted.
General Rules for Using DELETE
The following rules apply to using DELETE.
Deleting Rows Using Views
To delete table rows using a view through which the table is accessed, refer to the following conditions.
While it is true that you can generally convert the type of a view column (for example, from VARCHAR to CHARACTER), if that converted column is a component of an index, then that index is not used to delete rows from the base table because the data type of the recast column no longer matches the data type definition for that column in the index.
The resulting all-AMP, all-row behavior of the delete circumvents the performance advantages for which the index was designed.
Subqueries in a DELETE Statement
DELETE statement predicates can include subqueries that reference the delete target table, as well as other tables. The following DELETE statement is an example:
DELETE FROM publisher
WHERE 0 = (SELECT COUNT(*)
FROM book
WHERE book.pub_num=publisher.pub_num);
Two publishers have books in the library and two publishers do not.
The subquery executes once for each row of the outer reference, the publisher table. Because two publishers have no books in the library, the two rows that represent those publishers are deleted from the publisher table.
To modify this DELETE to use a noncorrelated subquery, change the subquery code to include all tables it references in its FROM clause.
DELETE FROM publisher
WHERE 0 = (SELECT COUNT(*)
FROM book, publisher
WHERE book.pub_num=publisher.pub_num);
When coded this way, the subquery predicate has a local defining reference, so the DELETE statement does not contain a correlated subquery. The count, determined once, is nonzero, so no rows are deleted.
Rules for Using Scalar Subqueries in a DELETE Statement
You can specify a scalar subquery in the WHERE clause of a DELETE statement in the same way you can specify one for a SELECT statement.
You can also specify a DELETE statement with a scalar subquery in the body of a trigger. However, Teradata Database processes any noncorrelated scalar subquery you specify in the WHERE clause of a DELETE statement in a row trigger as a single‑column single‑row spool instead of as a parameterized value.
Rules for Using Correlated Subqueries in a DELETE Statement
The following rules apply to correlated subqueries used in a DELETE statement:
A table_name must be added to specify the deleted table name in this case.
Correlation names are also referred to as range variables and aliases.
Also see “Correlated Subqueries” on page 134.
DELETE and Row-Partitioned Tables
The following rules apply to deleting rows from a row-partitioned table.
DELETE Statement is in the Last Request
If a DELETE statement is in the last request of a transaction and the following conditions are met, the delete is optimized to delete entire row partitions:
This avoids transient journaling of each row deleted from those row partitions.
Deferred Deletion Applies Only to Range Terms
Deferred deletions do not occur for partitioning levels that are not defined using a RANGE_N function. To qualify for deferred deletion, the test value of a RANGE_N partitioning expression must be a simple column reference for a partitioning level.
Deferred Deletion is Supported for LIKE terms
Deferred deletion is also supported for LIKE terms defined in the form partitioning_column LIKE ‘abc%’.
Deferred Deletion and Join Indexes
Teradata Database uses deferred partition deletion on PPI base tables and PPI join indexes for the following scenarios:
Deletion and Row-Partitioned Join Indexes
If you attempt to delete a row from a base table that causes an insert into a row-partitioned join index or an update of an index row in a row-partitioned join index such that:
the system aborts the request (ANSI mode) or transaction (Teradata mode). It does not perform the insert or update and returns an error or failure, respectively.
Deleting a row from a base table does not always cause the deletion of a join index on that base table. For example, you can specify a WHERE clause in the CREATE JOIN INDEX statement to create a sparse join index for which only those rows that meet the condition of the WHERE clause are inserted into the index, or, for the case of a row in the join index being updated in such a way that it no longer meets the conditions of the WHERE clause after the update, cause that row to be deleted from the index.
The process for this activity is as follows:
1 The system checks the WHERE clause condition for its truth value after the update to the row.
IF the condition evaluates to … |
THEN … |
FALSE |
the system deletes the row from the sparse join index. |
TRUE |
the system retains the row in the sparse join index and proceeds to stage b. |
2 The system evaluates the new result of the partitioning expression for the updated row.
IF the partitioning expression … |
THEN … |
|
the result is not between 1 and 65535 for the row. The system aborts the request (ANSI mode) or transaction (Teradata mode). It does not update the base table or the sparse join index, and returns an error or failure, respectively. |
|
the result is between 1 and 65535 for the row. The system stores the row in the appropriate partition, which might be different from the partition in which it was stored, and continues processing requests. |
Expression evaluation errors, such as divide by zero, can occur during the evaluation of a partitioning expression. The system response to such an error varies depending on the session mode in effect.
In this session mode … |
Expression evaluation errors roll back this work unit … |
ANSI |
request that contains the aborted request. |
Teradata |
transaction that contains the aborted request. |
Define your partitioning expressions to ensure that expression errors do not prevent the insertion of valid rows.
Collation and Row Deletion
Collation can affect the deletion of rows from tables defined with a character partitioning expression.
Partitioning Expressions and Unicode
If a partitioning expression for a table or noncompressed join index involves Unicode character expressions or literals and the system has been backed down to a release that has Unicode code points that do not match the code points that were in effect when the table or join index was defined, Teradata Database aborts any attempts to delete rows from the table and returns an error to the requestor.
Queue Tables and DELETE
The best practice is to avoid using the DELETE statement on a queue table because the operation requires a full table scan to rebuild the internal queue table cache. You should reserve this statement for exception handling.
A DELETE statement cannot be specified in a multistatement request that contains a SELECT and CONSUME request for the same queue table.
For details on queue tables and the queue table internal cache, see SQL Data Definition Language.
DELETE Support for NoPI and Column‑Partitioned Tables
DELETE is supported for NoPI and column‑partitioned tables.
For tables and rows deleted from a column-partitioned join index, rows are logically deleted. The rows are marked as deleted and the space is not reclaimed until an unconstrained DELETE is done or a row partition delete occurs for the partition that contains the logically deleted row.
DELETE has the following rules and restrictions for NoPI and column‑partitioned tables:
Note: Column-partition elimination and row-partition elimination can cause this to be less than a full-table scan.
Note: Deferred deletions for entire row partitions that are deleted reclaims space for the deleted row, including any logically deleted rows, in that row partition.
For more information about NoPI and column‑partitioned tables, see Database Design.
DELETE Support for External UDT Expressions
External UDT expressions are valid specifications for DELETE processing in the same way as any other valid expression. A UDT expression is any expression that returns a UDT value. See “Example 11: UDT Support For DELETE Statements” on page 359 for valid examples of using a UDT expression in a DELETE operation.
DELETE Support for SQL UDT Expressions
You can specify an SQL UDT as part of a WHERE clause condition in a DELETE statement if the SQL UDT returns a value expression. See “Example 13: Using an SQL UDF in a DELETE Request WHERE Condition” on page 360.
Embedded SQL and Stored Procedure Error Condition Handling
If DELETE is specified with a WHERE clause and the specified search condition fails because it selects no rows, the value ‘02000’ is assigned to SQLSTATE, +100 is assigned to SQLCODE, and no rows are deleted.
Example
This example deletes all rows with a department number of 500 from the employee table:
DELETE FROM employee
WHERE deptno=500;
Example
This example deletes all rows from the employee table (the ALL keyword may be omitted):
DELETE FROM employee ALL;
Example
This example deletes the row for employee 10011 from the employee table:
DELETE FROM employee
WHERE empno=10011;
Example : Join Condition DELETE
This example deletes rows from the employee table for employees who work in NYC. This operation joins the employee and department tables in the WHERE clause predicate:
DELETE FROM employee
WHERE employee.deptno = department.deptno
AND department.location = 'NYC';
Example : Self-Join Condition DELETE
This example deletes rows from the employee table for managers who have less work experience than their employees. This delete performs a self‑join on the employee table:
DELETE FROM employee AS managers
WHERE managers.deptno = employee.deptno
AND managers.jobtitle IN ('Manager', 'Vice Pres')
AND employee.yrsexp > managers.yrsexp;
Example
The following macro structures the SELECT and DELETE statements as a single multistatement request:
CREATE MACRO res_use
(from_date (DATE, DEFAULT DATE),
to_date (DATE, DEFAULT DATE),
from_time (INTEGER, DEFAULT 0),
to_time (INTEGER, DEFAULT 999999),
proc (VARCHAR(4), DEFAULT 'P'),
secs (SMALLINT, DEFAULT 600) )
AS (SELECT
the_date (TITLE 'Resource//TheDate'),
the_time (TITLE 'Utilized//TheTime'),
proc,
AVERAGE(hits) (TITLE 'Avg//Hits'),
MAXIMUM(cpu) (TITLE 'Max//CPU'),
AVERAGE(cpu) (TITLE 'Avg//CPU'),
MAXIMUM(disk) (TITLE 'Max//Disk'),
AVERAGE(disk) (TITLE 'Avg//Disk'),
MAXIMUM(host) (TITLE 'Max//Host'),
AVERAGE(host) (TITLE 'Avg//Host'),
MAXIMUM(chan) (TITLE 'Max//Chan'),
AVERAGE(chan) (TITLE 'Avg//Chan')
FROM DBC.res_use_view
GROUP BY the_date, the_time, proc
WHERE the_date BETWEEN :from_date AND :to_date
AND the_time BETWEEN :from_time AND :to_time
AND proc CONTAINS :proc
AND secs EQ :secs
ORDER BY proc, the_date, the_time
;DELETE FROM res_use_view ALL;);
If the preceding macro is executed in Teradata session mode, but not within the boundaries of an explicit transaction, fastpath delete processing is used for the DELETE statement. See “Unconstrained Fastpath Delete Processing” on page 349.
In ANSI session mode, the system uses fastpath delete processing for the DELETE statement when a COMMIT is included in the macro or when a COMMIT is specified at the end of the request line, and immediately following the execution of the macro, within the request.
For example, the last line of the preceding macro would read as follows:
DELETE FROM res_use_view ALL ;
COMMIT;) ;
The system uses slow path processing for the DELETE statement when no COMMIT is stated in the macro itself or within the request that executes the macro.
Example
The DELETE … ALL statement in the following BTEQ request invokes fastpath delete processing. The statements are combined into a multistatement request and are processed as an implicit transaction in Teradata session mode. Note that DELETE is the last statement in the request:
SELECT *
FROM DBC.log_on_off_v
WHERE log_date = DATE
AND user_name = 'Administrator'
;SELECT log_time, user_name, event, account_name
FROM DBC.log_on_off_v
WHERE log_date = DATE
AND user_name NOT IN ('Administrator', 'SecAdmin', 'Oper')
;SELECT log_time, event, logical_host_id, pe_no
FROM DBC.log_on_off_v
WHERE log_date = DATE
AND user_name = 'Oper'
;DELETE FROM DBC.log_on_off_v ALL ;
In ANSI session mode, the COMMIT statement must follow the DELETE request, so the last line of the above example would read:
DELETE FROM DBC.log_on_off_v ALL ;
COMMIT;
Example : Join Condition DELETE With FROM Clause and Correlation Name
The following example deletes employees from the employee table using a join on the employee and department tables.
DELETE employee
FROM department AS d, employee
WHERE employee.dept_no = d.dept_no
AND salary_pool < 50000;
Example : Join Condition DELETE With No FROM Clause
The following example uses different syntax to perform the same action as “Example 8: Join Condition DELETE With FROM Clause and Correlation Name” on page 359. Because this DELETE statement does not specify a FROM clause, you cannot use correlation names for the tables.
DELETE employee
WHERE employee.dept_no = department.dept_no
AND salary_pool < 50000;
Example : Join Condition DELETE With Derived Table Subquery
The following example uses a subquery to perform the same action as “Example 8: Join Condition DELETE With FROM Clause and Correlation Name” on page 359 and “Example 9: Join Condition DELETE With No FROM Clause” on page 359.
DELETE FROM employee
WHERE dept_no IN (SELECT dept_no
FROM department
WHERE salary_pool < 50000);
Example : UDT Support For DELETE Statements
The following examples show valid use of UDT expressions in a DELETE statement:
DELETE FROM test_table
WHERE circle_udt < NEW circle('1,1,9');
DELETE FROM test_table1, test_table2
WHERE test_table1.mycirc = test_table2.mycirc;
Example : DELETE and NoPI Tables
The following examples are based on this NoPI table definition:
CREATE TABLE new_sales,
FALLBACK (
item_nbr INTEGER NOT NULL,
sale_date DATE FORMAT 'MM/DD/YYYY' NOT NULL,
item_count INTEGER)
NO PRIMARY INDEX;
The following DELETE statement requires a full-table scan because new_sales has neither a primary nor a secondary index.
DELETE FROM new_sales
WHERE item_nbr = 100;
The following DELETE statement uses fastpath delete processing if it is submitted in Teradata session mode as an implicit transaction.
DELETE FROM new_sales;
The following DELETE statement uses fastpath delete processing when it is submitted in Teradata session mode as a single request.
BEGIN TRANSACTION
;DELETE FROM new_sales
;END TRANSACTION;
Assume the following single-statement request is submitted in Teradata session mode.
BEGIN TRANSACTION;
Then the following DELETE statement uses fastpath delete processing when it is submitted with an END TRANSACTION statement in a multistatement request.
DELETE FROM new_sales
;END TRANSACTION;
The following DELETE statement uses fastpath delete processing when it is submitted with a COMMIT statement in ANSI mode in a multistatement request.
DELETE FROM new_sales
;COMMIT;
Example : Using an SQL UDF in a DELETE Request WHERE Condition
You can specify an SQL UDF in a WHERE clause search condition if it returns a value expression.
DELETE FROM t1
WHERE a1 = test.value_expression(b1, c1);
Example : Deleting a Row From a Table With Row-Level Security Protection
In this example, assume that the user submitting the request has the privileges required to delete a row from inventory.
The following DELETE statement deletes a row from a table named inventory.
DELETE FROM inventory
WHERE col_1 = 12126;
Example : Application of Row-Level Security DELETE and SELECT Constraints When User Lacks Required Privileges (DELETE)
This example shows how the DELETE and SELECT constraints are applied when a user without the required OVERRIDE privileges attempts to execute a DELETE statement on a table that has the row-level security DELETE and SELECT constraints.
An EXPLAIN request modifier is used to show the steps involved in the execution of the request and the outcome of the application of the constraints.
Table Definition
The statement used to create the table in this example is:
CREATE TABLE rls_tbl(
col1 INT,
col2 INT,
classification_levels CONSTRAINT,
classification_categories CONSTRAINT);
User’s Session Constraint Values
The user’s session constraint values are:
Constraint1Name LEVELS
Constraint1Value 2
Constraint3Name CATEGORIES
Constraint3Value '90000000'xb
EXPLAIN Request Modifier with a DELETE Statement
This EXPLAIN request modifier shows the steps performed to execute the DELETE statement and the outcome of the application of the DELETE and SELECT constraints.
EXPLAIN DELETE FROM rls_tbl WHERE col1=2;
EXPLAIN Text
The system returns this EXPLAIN text.
*** Help information returned. 10 rows.
*** Total elapsed time was 1 second.
Explanation
----------------------------------------------------------------------
1) First, we do a single-AMP DELETE from RS.rls_tbl by way of the
primary index "RS.rls_tbl.col1 = 2" with a residual condition of (
"((SYSLIB.DELETELEVEL (RS.rls_tbl.levels ))= 'T') AND
(((SYSLIB.SELECTLEVEL (2, RS.rls_tbl.levels ))= 'T') AND
(((SYSLIB.DELETECATEGORIES (RS.rls_tbl.categories ))= 'T') AND
((SYSLIB.SELECTCATEGORIES ('90000000'XB, RS.rls_tbl.categories ))=
'T')))"). The size is estimated with no confidence to be 2 rows.
The estimated time for this step is 0.01 seconds.
-> No rows are returned to the user as the result of statement 1.
The total estimated time is 0.01 seconds.