16.10 - Using Delete Task - Parallel Transporter

Teradata Parallel Transporter Reference

Product
Parallel Transporter
Release Number
16.10
Published
July 2017
Content Type
Programming Reference
Publication ID
B035-2436-077K
Language
English (United States)

Using the Delete Task option of the Update operator can run as either a consumer operator or as a standalone operator, depending on the construction of the APPLY statement:

  • As a standalone operator that does not attempt to retrieve a row from the data stream:
    APPLY
         <SQL DELETE statement>
  • As a consumer operator that attempts to read a row from the data stream:
    APPLY
         <SQL DELETE statement>
    SELECT FROM …

Specify a single DELETE statement in the APPLY statement:

   APPLY
(
     'DELETE FROM TABLE xyz;'
) ;

In this case, the Update operator runs as a standalone operator. (The Update operator is a consumer operator and there is no producer operator, so there is no SELECT statement).

You can also specify a DELETE statement in which information in the DELETE requires some data. In this case, the APPLY needs a SELECT statement:

   APPLY
(
       'DELETE FROM TABLE xyz WHERE Field1 = :Field1;'
   )
   SELECT * FROM OPERATOR (FILE_READER [1] . . .
   ;

In this case, the Update operator is running as a consumer operator and it requires exactly one row of data. That row of data is passed to the Teradata Database, which extracts the data from the column as specified in the WHERE clause.

Another example, similar to the first one, is where the Update operator runs as a standalone operator but the DELETE statement has a WHERE clause:

   APPLY
   (
       'DELETE FROM TABLE xyz WHERE Field1 = ''abc'';'
   ) ;

In this case, there is a WHERE clause, but the information in the WHERE clause does not require data from a producer operator.

When the Update operator runs as a standalone operator, no schema is necessary. That is, you do not need to define a schema using the DEFINE SCHEMA statement. This is because no data is needed from a producer operator for the job.