delete (positioned cursor)

Removes, from a table, the row indicated by the current cursor position for an open cursor.

exec sql [at connection_name] delete 
 [from] table_name 
 where current of cursor_name;

where current of cursor_name: Causes Adaptive Server to delete the row of the table indicated by the current cursor position for the cursor cursor_name.

exec sql include sqlca;

main()

     char answer[1];

exec sql begin declare section;

     CS_CHAR disc_type[40];

     CS_CHAR store_id[5];

     CS_SMALLINT ind_store_id;

exec sql end declare section;

exec sql connect “sa”;

exec sql use pubs2;

exec sql declare purge_cursor cursor for

     select discounttype, stor_id

     from discounts;

exec sql open purge_cursor;

exec sql whenever not found goto alldone;

while (1)

     exec sql fetch purge_cursor into :disc_type, :store_id

            :ind_store_id;

     if (ind_store_id != -1)

          printf(“%s, %s\n”, disc_type, store_id);

          printf(“Delete Discount Record? (y/n)  >”);

          gets(answer);

          if (strncmp(answer, “y”, 1) == 0)

               exec sql delete from discounts where

                    current of purge_cursor;

/*

 **  No changes will be committed to the database because

 **  this program does not contain an “exec sql commit work;”

 **  statement.  The changes will be rolled back when the

 **  user disconnects.

*/

alldone:

     exec sql close purge_cursor;

     exec sql disconnect all;

This reference page mainly describes aspects of the Transact-SQL delete statement that differ when used with Embedded SQL. See the Adaptive Server Enterprise Reference Manual for more information about the delete statement.
This form of the delete statement must execute on the connection where the cursor cursor_name was opened. If the delete statement includes the at connection_name clause, the clause must match the at connection_name clause of the open cursor statement that opened cursor_name.
The delete statement fails if the cursor was declared for read only, or if the select statement included an order by clause.