DELETE Syntax | SQL Statements | Teradata Vantage - DELETE Syntax - Advanced SQL Engine - Teradata Database

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.