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];
The name of the table from which this delete statement deletes rows.
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.
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.
If you need to remove rows specified by the current position of a cursor pointer, use the delete (positioned cursor) statement.
close, declare cursor, fetch, open, update
