Deleting cursor result set rows

Using the where current of clause of the delete statement, you can delete the row at the current cursor position. When you delete a row from the cursor’s result set, the row is deleted from the underlying database table. You can delete only one row at a time using the cursor.

The syntax for delete...where current of is:

delete [from] 
     [[database.]owner.] {table_name | view_name}
     where current of cursor_name 

The table_name or view_name specified with a delete...where current of must be the table or view specified in the first from clause of the select statement that defines the cursor.

For example, you can delete the row that the authors_crsr cursor currently points to by entering:

delete from authors
where current of authors_crsr

The from keyword in the above example is optional.

NoteYou cannot delete a row from a cursor defined by a select statement containing a join, even if the cursor is updatable.

After you delete a row from a cursor, Adaptive Server positions the cursor before the row following the deleted row in the cursor result set. You must still use fetch to access that row. If the deleted row is the last row in the cursor result set, Adaptive Server positions the cursor after the last row of the result set.

For example, after deleting the current row in the example (the author Michel DeFrance), you can fetch the next three authors in the cursor result set (assuming that cursor rows is still set to 3):

fetch authors_crsr
au_id        au_lname            au_fname
-----------  ------------------- ---------------
807-91-6654  Panteley            Sylvia
899-46-2035  Ringer              Anne
998-72-3567  Ringer              Albert
 
(3 rows affected)

You can delete a row from the base table without referring to a cursor. The cursor result set changes as changes are made to the base table.