In ANSI mode, you can define a cursor for the query results and for every row in the query results in order to update or delete the data row via the cursor associated with the row.
This means that update and delete operations do not identify a search condition; instead, they identify a cursor (or a pointer) to a specific row to be updated or deleted.
You can use pocketable cursors to update each row of a select result independently as it is processed.
Recommendations
To reap the full benefit from the updatable cursor feature, you should minimize:
- The size of query result and number of updates/transaction
- The length of time you hold the cursor open
Using many updates per cursor may not be optimal because:
- They block other transactions.
- The system requires longer rollbacks.
In this case, use the MultiLoad utility to do updates.