To update or delete the current row of a cursor, specify the 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 < 25;
exec sql open c1;
for (;;)
{
exec sql fetch c1 into :title, :roy, :sales;
if (SQLCODE == 100) break;
if (sales > 10000)
exec sql update titles
set royalty = :roy + 2
where current of c1;
}
exec sql close c1;
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.
