Basic Form
{ DELETE | DEL } [ with_isolated_loading ]
[ delete_table_name | correlation_name ]
FROM table_spec
[ WHERE condition | ALL ] [;]
Join Condition Form
{ DELETE | DEL } [ with_isolated_loading ]
{ delete_table_name | correlation_name }
[ FROM table_spec [,...] ]
[ WHERE condition | ALL ] [;]
Syntax Elements
-
with_isolated_loading
-
WITH [NO] [CONCURRENT] ISOLATED LOADING
- Without NO, the DELETE can be performed as a concurrent load isolated operation.
- With NO, the DELETE is not performed as a concurrent load isolated operation.
- CONCURRENT can be included for readability.
-
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. They are also referred to as 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 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, then the row in delete_table is deleted; else it is not.
- 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 .
- You should also specify the names of all outer tables, including the table from which rows are to be deleted.