Update and Delete Using WHERE CURRENT OF

In a stored procedure, 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

table_name

A table or view defined in the FROM clause of the cursor definition query. If table_name references a view, the view must be updatable (see Using Updatable Views).

cursor_name

The name that was associated with the cursor using DECLARE.

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.