Using Delete Task
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:
APPLY
<SQL DELETE statement>
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.
Note: 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.