DELETE - Parallel Data Pump

DELETE

Purpose  

Teradata TPump supports the DELETE Teradata SQL statement, which removes rows from a table.

Syntax  

where

Syntax Element	Description
tname	Table from which rows are to be deleted tname is qualified either explicitly by database name, or by the current default database.
WHERE condition	Conditional clause identifying the row(s) to delete The conditional clause uses values from input data record fields as defined by a FIELD command or TABLE command of the layout referenced by an IMPORT using this statement.

Usage Notes

The following notes describe how to use DELETE statements following a DML command.

A DELETE statement may also be used in the support environment; normal rules for DELETE are followed in that case.

Teradata TPump operates only on single table statements so DELETE statements must not contain any joins.

To delete records from a table, the username specified on the LOGON command must have DELETE privilege on the specified table.

When the condition(s) of the DELETE statement WHERE clause are evaluated, the result can be definitely true, definitely false, or indeterminate. If the result is true for a specific row, Teradata TPump deletes the row. An indeterminate result, due to an abnormal arithmetic condition such as underflow, overflow, or division by zero, is treated as an error, and Teradata TPump records both row and error code in the error table.

The DELETE statement must identify only one object.

Remember the following when constructing scripts:

A DELETE statement can be applied to either a table or view, provided that the view does not specify a join.

Equality values for all the primary index columns should normally be specified in the WHERE clause.

The OR construct can be used in the WHERE clause of a DELETE statement; alternatively, two or more separate DML statements (one per OR term) can be used, with the DML statements applied conditionally with the APPLY clause of the IMPORT command. The nature of the alternatives will usually make one of the methods more appropriate.

The column(s) specified in this clause need not be a part of any index, but can be one or more nonindexed columns. This clause may specify nonequality values for any combination of columns of unique indices, or any values for other columns.

The maximum number of INSERT, UPDATE, and DELETE statements that can be referenced in an IMPORT is 128. The 128th DML which would cause the insertion of the DML sequence number of 128 for the DMLSEQ field in the error table could lead to Teradata Database 3520 error.

The maximum number of DML statements that can be packed into a request is 1500. The default number of statements packed is 20.

DML validtime qualifier and NONTEMPORAL semantics are supported. For more information, see SQL Data Manipulation Language (B035‑1146).

Example  

The following example uses an input data source containing a series of one‑field, four‑byte records. Each record contains the value (EmpNum) of the primary index column (EmpNo) of a row to be deleted from the Employee table.

.BEGIN LOAD SESSION number;

.LAYOUT Layoutname; 

.FIELD EmpNum 1 INTEGER; 

.DML LABEL DMLlabelname; 

DELETE Employee WHERE EmpNo = :EmpNum; 

.IMPORT INFILE Infilename LAYOUT Layoutname  APPLY DMLlabelname;

.END LOAD;