To update or delete the current row of a cursor, specify where current of cursor_name as the search condition in an update or delete statement.
To update rows through a cursor, the result columns to be used in the updates must be updatable. They cannot be the result of SQL expressions such as max(colname). In other words, there must be a valid correspondence between the result column and the database column to be updated.
The following example demonstrates how to use a cursor to update rows:
exec sql declare c1 cursor for
select title_id, royalty, ytd_sales
from titles
where royalty < 12
end-exec
exec sql open C1 end-exec
PERFORM READ-PARA UNTIL SQLCODE = 100.
exec sql close C1 end-exec.
STOP RUN.
READ-PARA.
exec sql fetch C1 into :TITLE-ID, :ROYALTY,
:SALES end-exec.
IF SALES > 10000
exec sql update titles
set royalty = :roy + 2
where current of C1 end-exec.
The Embedded SQL syntax of the update and delete statements is the same as in Transact-SQL, with the addition of the where current of cursor_name search condition.
For details on determining table update protocol and locking, see the Transact-SQL User’s Guide.
