Understanding positioned update or delete

When declaring a database cursor with a SELECT statement using a unique table and ending with the FOR UPDATE keywords, you can update or delete database rows by using the WHERE CURRENT OF keywords in the UPDATE or DELETE statements. Such an operation is called positioned update or positioned delete.

Some database servers do not support hold cursors (WITH HOLD) declared with a SELECT statement including the FOR UPDATE keywords. The SQL standards require for update cursors to be automatically closed at the end of a transaction. Therefore, it is strongly recommended that you use positioned updates in a transaction block.

Do not confuse positioned update with the use of SELECT FOR UPDATE statements that are not associated with a database cursor. Executing SELECT FOR UPDATE statements is supported by the language, but you cannot perform positioned updates since there is no cursor identifier associated to the result set.

To perform a positioned update or delete, you must declare the database cursor with a SELECT FOR UPDATE statement.

Figure 1. SELECT FOR UPDATE statement

Then, start a transaction, open the cursor and fetch a row.

Figure 2. Open a cursor

Finally, you update or delete the current row and you commit the transaction.

Figure 3. Delete the row