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. Search conditions can be combined or can be formed 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 Sales (and put it back)

In this exercise, you first look to see what orders are older than a certain date. Then you delete those sales from the Sales table and check to see that they are gone.

1. Enter the following SELECT statement to see what sales were ordered prior to 1992:

    SELECT * FROM Sales WHERE order_date < '31-DEC-1991'
   

   There should be only one order returned. Notice that the SELECT statement requires that you specify columns. You can also use “*” to specify all columns.

2. Commit your data to this point by selecting Transaction > Commit (or F9).
3. Enter the following DELETE statement:

    DELETE FROM Sales WHERE order_date < '31-DEC-1991'
   

   Tip: To make it easier, you can display the previous SELECT statement and change “SELECT *” by DELETE. You can use the Previous Statement button to display previous statements.

   Notice that the DELETE statement does not take any column specification. That is because it deletes all columns for the rows you have specified.

4. Now repeat your original SELECT query. There should be no rows returned.
5. However, you just realized that you did not want to delete that data after all. Fortunately, you committed previous work before executing this statement, so choose Transaction > Rollback and click OK at the prompt. This “undoes” all statements that were executed since the last Commit.
6. Perform the SELECT again to see that the deleted row is back.

Time to back up: Now that you have created your database and its tables and finished inserting and updating data, this is a good time to back up your database to Tutorial6.ibk.

Deleting More Precisely

You can restrict deletions further by combining search conditions. For example, enter the following statement to delete 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 entering this statement, but you will get an error because there is a foreign key column in the Employee_project table that references the Employee table. If you were to delete these rows, some values in the Employee_project table would no longer have matching values in the Employee table, violating the foreign key constraint that says any employee who has a project must also have an entry in the Employee table.

In addition, you can use subqueries to delete data, just as you use them to update. The following statement would delete all rows from the Employee table where the employees were 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 would violate foreign key constraints on other tables.

Advance To:

• Part IV