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