Step 5b: Deleting the current row

 

When your program has retrieved the current row, you can delete the row by using the DELETE statement with the WHERE CURRENT OF clause. The WHERE CURRENT OF clause specifies a cursor that points to the row that you want to delete.

The DELETE ... WHERE CURRENT OF statement looks like this:

EXEC SQL  DELETE FROM table-name    WHERE CURRENT OF cursor-name END-EXEC.

When used with a cursor, the DELETE statement:

• Deletes only one row—the current row

• Uses the WHERE CURRENT OF clause to identify a cursor that points to the row to be deleted

After you delete a row, you cannot update or delete another row using that cursor until you issue a FETCH statement to position the cursor.

You can use the DELETE statement to delete all rows that meet a specific search condition. You can also use the FETCH and DELETE ... WHERE CURRENT OF statements when you want to obtain a copy of the row, examine it, and then delete it.

 

Parent topic:

Examples: Using a cursor