Deleting Data (Embedded SQL Guide)
Go Up to Working with Data
To remove rows of data from a table, use the DELETE
statement. To delete rows a user or procedure must have DELETE
privilege for the table.
The syntax of DELETE
is:
DELETE [TRANSACTION name] FROM table WHERE <search_condition> | WHERE CURRENT OF cursorname [ORDER BY <order_list>] [ROWS <value> [TO <upper_value>] [BY <step_value>][PERCENT][WITH TIES]];
DELETE
irretrievably removes entire rows from the table specified in the FROM
clause, regardless of each column’s data type.
A single DELETE
can be used to remove any number of rows in a table. For example, the following statement removes the single row containing “Channel Marketing” from the DEPARTMENT
table:
EXEC SQL DELETE FROM DEPARTMENT WHERE DEPARTMENT = 'Channel Marketing';
The WHERE
clause in this example targets a single row for update. If the same deletion criteria apply to a number of rows in a table, the WHERE
clause can be more general. For example, to remove all rows from the DEPARTMENT
table with BUDGET
values < $1,000,000, the DELETE
statement would be as follows:
EXEC SQL DELETE FROM DEPARTMENT WHERE BUDGET < 1000000;
Using DELETE
to remove a number of rows is sometimes called a mass delete.
The WHERE
clause in a DELETE
statement can contain a subquery that references one or more other tables. For a discussion of subqueries, see Using Subqueries.