Update and Delete Using WHERE CURRENT OF

In a stored procedure, user defined function or trigger, update or delete a row at which the cursor is currently positioned.

Syntax

DELETE FROM table_name ... WHERE CURRENT OF cursor_name
UPDATE table_name SET ... WHERE CURRENT OF cursor_name

Description

This will update or delete the record that was last fetched by a cursor. The cursor must have been defined FOR UPDATE if this is an UPDATE statement. DELETE can be performed on a cursor that was not declared as FOR UPDATE, assuming that it was declared with an updatable query.

See DELETE and UPDATE.

Parameters

Note: AUTOCOMMIT_SP_MODE should be disabled if using an updatable cursor. A COMMIT will invalidate the last fetched record causing any subsequent DELETE/UPDATE...WHERE CURRENT OF to throw an exception.