8. SQL Statements : DELETE : Embedded Usage : Cursor Delete
 
Share this page                  
Cursor Delete
The cursor version deletes the row to which the specified cursor is pointing. If the cursor is not currently pointing at a row when the delete is executed, the DBMS Server generates an error.
To position the cursor to a row, issue a FETCH statement. After a deletion, the cursor points to a position after the deleted row, but before the next row, if any.
If the cursor is opened for direct update, the deletion takes effect immediately. If the cursor is opened for deferred update, the deletion takes effect when the cursor is closed. If the cursor is opened for deferred update, a row cannot be deleted after it has been updated. If an attempt is made to do so, the DBMS Server returns an error indicating an ambiguous update operation.
Both the COMMIT and ROLLBACK statements close all open cursors. A common programming error is to delete the current row of a cursor, commit the change and continue in a loop to repeat the process. This process fails because the first commit closes the cursor.
A cursor delete can be executed dynamically using the PREPARE and EXECUTE statements. However, a cursor delete can only be prepared after the referenced cursor is opened. The prepared cursor delete remains valid while the cursor is open. If the named cursor is closed and reopened, the corresponding DELETE statement must be prepared again. If an attempt is made to execute the DELETE statement associated with the previously open cursor, the DBMS Server returns an error.
In performing a cursor delete, make sure that certain conditions are met:
A cursor must be declared in the same file in which any DELETE statements referencing that cursor appear. This applies also to any cursors referenced in dynamic DELETE statement strings.
A cursor name in a dynamic DELETE statement must be unique among all open cursors in the current transaction.
The cursor stipulated in the delete must be open before the statement is executed.
The SELECT statement of the cursor must not contain a DISTINCT, GROUP BY, HAVING, ORDER BY, or UNION clause.
The FROM clause of the delete and the FROM clause in the cursor's declaration must refer to the same database table.