Basic Form
{ DELETE | DEL }
[ delete_table_name | correlation_name ]
FROM table_spec
[ WHERE condition | ALL ] [;]
Join Condition Form
{ DELETE | DEL }
{ delete_table_name | correlation_name }
[ FROM table_spec [,...] ]
[ WHERE condition | ALL ] [;]
Syntax Elements
- 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.
- correlation_name
- Optional table alias name.
- You must specify a correlation name for each table specified in a self-join.
- ANSI calls table aliases correlation names. Aliases are also called range variables.
- table_spec
table_name [ [ AS ] correlation_name ]
- condition
- 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 in SELECT Statements and Rules for Using Scalar Subqueries in a DELETE Statement for details.
- ALL
- 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.
- table_name
- The name of the table, queue table, or view on delete_table_name from which the DELETE operation is to remove rows.
- 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, the row in delete_table is deleted. Otherwise, that row is not deleted.
- See Rules for Using Scalar Subqueries in a DELETE Statement 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: Join Condition DELETE with FROM Clause and Correlation Name and Example: Join Condition DELETE with No FROM Clause .
- Specify the names of all outer tables, including the table from which rows are to be deleted.