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.
You 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.
