Deleting Data (Embedded SQL Guide)

From InterBase

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.

Topics

Advance To: