Usage Notes - MultiLoad

Teradata® MultiLoad Reference

Product
MultiLoad
Release Number
17.00
Published
June 2020
Language
English (United States)
Last Update
2020-06-18
dita:mapPath
aim1544831946660.ditamap
dita:ditavalPath
gyk1507317446489.ditaval
dita:id
B035-2409
lifecycle
previous
Product Category
Teradata Tools and Utilities

The following table describes the things to consider when using the DELETE statement.

DELETE Considerations 
Topic Usage Notes
Evaluating the Conditional Clause The result of evaluating the conditional clause for a row can be either positive, negative, or indeterminate:
  • If positive, then Teradata MultiLoad deletes the row.
  • If negative, then Teradata MultiLoad does nothing.
  • If indeterminate, then Teradata MultiLoad treats the result as an error condition and records the row and error number in the error table.
Indeterminate results are caused by an abnormal arithmetic condition, such as underflow, overflow, or division by zero.
Joins Teradata MultiLoad operates only on single-table statements. DELETE statements cannot contain joins.
Required Privilege To use the DELETE statement, the DELETE privilege on the specified table or view is required.
Using DELETE in Import Tasks If a BEGIN MLOAD command was used to specify an import task:
  • DELETE statements can be applied to either a table or a view, provided that the view does not specify a join.
  • The number of input data records is unlimited.
  • Equality values must be specified for all the primary index columns in the WHERE clause.
  • Values must be specified for the partitioning columns if the target table has PPI.
  • The DELETE statement must be preceded with a DML LABEL command.
  • The OR construct in the WHERE clause of a DELETE statement cannot be used. Instead, use two separate DELETE statements and apply them conditionally with the APPLY clause of the IMPORT command.
Using DELETE in Delete Tasks If a BEGIN DELETE MLOAD command is used to specify a delete task:
  • The single DELETE statement can be applied to a table only, not to a view.
  • The DELETE operation requires a full file scan.
  • Do not specify Equality values for all the primary index columns in the WHERE clause. However, equality values for a lesser number of columns of a unique primary index can be specified.

    The columns 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.

  • Do not precede the DELETE statement with a DML LABEL command. The single DELETE statement is always applied.
  • Optionally, provide a single imported data record, properly defined by a LAYOUT command and its associated FIELD commands (and, optionally, FILLER commands), which is then specified in the single IMPORT command. The data record provides values for the WHERE clause of the DELETE statement.

In the absence of an IMPORT command, the WHERE clause of the DELETE statement must statically specify the rows to be deleted, without reference to any dynamic data from an imported data record.