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 [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.
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
EXEC SQL DELETE FROM DEPARTMENT WHERE DEPARTMENT = 'Channel Marketing';
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;
DELETE to remove a number of rows is sometimes called a mass delete.
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.