The first syntactical form, called a searched delete, removes all rows identified by the table name and WHERE clause.
The second syntactical form, called a positioned delete, deletes
the current row of an open, updatable cursor. If there is no current row or
if it no longer satisfies the cursor's query, an exception is raised. For
more information about updatable cursors, see
A searched delete statement depends on the table being updated, all of its conglomerates (units of storage such as heaps or indexes), and any other table named in the WHERE clause. A CREATE or DROP INDEX statement for the target table of a prepared searched delete statement invalidates the prepared searched delete statement.
The positioned delete statement depends on the cursor and any tables the cursor references. You can compile a positioned delete even if the cursor has not been opened yet. However, removing the open cursor with the JDBC close method invalidates the positioned delete.
A CREATE or DROP INDEX statement for the target table of a prepared positioned delete invalidates the prepared positioned delete statement.