delete (searched)

Removes rows specified by search conditions.

exec sql [at connection_name] delete table_name_1 
 [from table_name_n 
 [, table_name_n]…] 
 [where search_conditions];

table_name_1: The name of the table from which this delete statement deletes rows.

from table_name_n: The name of a table to be joined with table_name_1 to determine which rows of table_name_1 will be deleted. The delete statement does not delete rows from table_name_n.

where search_conditions: Specifies which rows will be deleted. If you omit the where clause, the delete statement deletes all rows of table_name_1.

/*

 ** Function to FAKE a cascade delete of an author **

 **by name -- this function assumes that pubs2 is

 ** the current database.

 ** Returns 1 for success, 0 for failure

**/

     int        drop_author(fname, lname)

     char       *fname;

     char       *lname;

 exec sql begin declare section;

     CS_CHAR        f_name[41], l_name[41];

     CS_CHAR        titleid[10], auid[10];

 exec sql end declare section;

     long        SQLCODE;

 strcpy(f_name, fname);

 strcpy(l_name, lname);

 exec sql whenever sqlerror goto roll_back;

 exec sql select au_id from authors into :auid

         where au_fname = :f_name

         and au_lname = :l_name;

exec sql delete from au_pix where au_id = :auid;

exec sql delete from blurbs where au_id = :auid;

 exec sql declare cur1 cursor for

     select title_id from titleauthor

     where au_id = :auid;

 exec sql open cur1;

 while (SQLCODE == 0)

     exec sql fetch cur1 into :titleid;

    if(SQLCODE == 100) break;

    exec sql delete from salesdetail

         where title_id = :titleid;

     exec sql delete from rowsched

         where title_id = :titleid;

     exec sql delete from titles

         where title_id = :titleid;

     exec sql delete from titleauthor

         where current of cur1;

 exec sql close cur1;

 exec sql delete from authors

     where au_id = :auid;

 exec sql commit work;

 return 1;

 roll_back:

     exec sql rollback work;

     return 0;

This reference page describes mainly 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.
If you need to remove rows specified by the current position of a cursor pointer, use the delete (positioned cursor) statement.