Using the WHERE CURRENT OF Keywords (ESQL/C, SPL)

The WHERE CURRENT OF clause deletes the current row of the active set of a cursor. When you include this clause, the DELETE statement removes the row of the active set at the current position of the cursor. After the deletion, no current row exists; you cannot use the cursor to delete or update a row until you reposition the cursor with a FETCH statement (in ESQL/C routines) or with a FOREACH statement (in SPL routines).

You access the current row of the active set of a cursor with an update cursor. Before you can use the WHERE CURRENT OF clause, you must first create an update cursor by using the FOREACH statement (in SPL) or the DECLARE statement with the FOR UPDATE clause (in ). The cursor_id that follows the OF keyword cannot be declared, however, by the DECLARE statement in an SPL routine

Unless they are declared with the FOR READ ONLY keywords, all Select cursors are potentially update cursors in an ANSI-compliant database. You can use the WHERE CURRENT OF clause with any Select cursor that was not declared with the FOR READ ONLY keywords.

You cannot use WHERE CURRENT OF if you are selecting from only one table in a table hierarchy. That is, this clause is not valid with the ONLY keyword.

The WHERE CURRENT OF clause can be used to delete an element from a collection by deleting the current row of the collection-derived table that a collection variable holds. For more information, see Collection-Derived Table.