Deleting Data

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.

Important: 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. To make it easier, you can display the previous SELECT statement and substitute DELETE for “SELECT *”. You can use the Previous Statement button to display previous statements:

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

   Notice that the DELETE statement does not take any column specification. That’s 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 didn’t 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.gbk.

Advance To:

Deleting More Precisely