DELETE Syntax | SQL Statements | Teradata Vantage - DELETE Syntax - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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.