InterBase Quick Start: Part III - Deleting Data

Go Up to InterBase Quick Start: Part III - Populating the Database

To remove one or more rows of data from a table, use the DELETE statement. A simple DELETE has the following syntax:

DELETE FROM Table
WHERE  CONDITION

As with UPDATE, the WHERE clause specifies a search condition that determines the rows to delete. You can combine search conditions form them using a subquery.

Note:
The DELETE statement does not require a WHERE clause. However, if you do not include a WHERE clause, you delete all the rows of a table.

Deleting a Row From the Sales Table

Enter the following SELECT statement to see what sales were ordered prior to 1992:
```
SELECT *
FROM   Sales
WHERE  order_date < '31-DEC-1991'
```
The result that you get should only contain one entry.
Commit your work up to this point.
Enter the following DELETE statement:
```
DELETE FROM Sales
WHERE  order_date < '31-DEC-1991'
```
Notice that the DELETE statement does not expect any column specification. That is because it deletes all columns for the rows that you specifiy.
Enter the SELECT query from step 1. You should get an empty result (no rows).

Using Rollback to Undo Changes

You just realized that you did not want to delete that data after all. Fortunately, you committed your work before executing the DELETE statement, so now you can Rollback (undo) the changes that you performed after the last commit.

Perform a Rollback.
Enter the SELECT query from step 1 again. You should see the same result (1 row) as before you deleted the data.

Time to Back up

If you have successfully completed this section, it is a good time to back up your database.

Deleting More Precisely

You can restrict a delete operation further by combining search conditions. For example, the following statement deletes records of everyone in the sales department hired before January 1, 1992:

DELETE FROM Employee
WHERE  job_code = 'Sales'
       AND hire_date = '01-Jan-1992'

You can try to execute this statement, but you get an error because there is a foreign key column in the Employee_project table that references the Employee table. Deleting these rows would leave some values in the Employee_project table without matching values in the Employee table, and that violats the foreign key constraint that says: "Any employee that has a project must also have an entry in the Employee table".

You can also use subqueries to delete data, just as you use them to update. The following statement deletes all rows from the Employee table where the employees are in the same department as Katherine Young.

DELETE FROM Employee
WHERE  dept_no = (SELECT dept_no
                  FROM   Employee
                  WHERE  full_name = 'Young, Katherine')

Again, you cannot actually execute this statement because it violates foreign key constraints on other tables.

Advance To:

Part IV - Retrieving Data