The Delete Task option is unique to the Update operator. It deletes rows more quickly than using a single DELETE SQL statement.
When the DeleteTask attribute is set to 'Y', rows are deleted from a single table based on values other than a unique primary index (UPI) equality condition. A Delete Task option uses a single session and a single instance.
The Delete Task option is a good choice when a large percentage of rows in a table need to be deleted, such as deleting all rows with a transaction date prior to a specified date.
The Delete Task option operates very similarly to the standard DELETE statement in the Update operator, with the following differences:
When the Delete Task option is specified, the Update operator functions as a standalone operator, that is, not as the usual consumer operator that reads from a source data stream. The exception is when the Delete Task is invoked by an APPLY statement that includes a WHERE clause, and the source data stream contains only a single row. In this case, the Update operator with the Delete Task option still functions as a consumer operator.
The following rules apply to a Delete Task operation regardless of whether it functions as a standalone or consumer operator: