SQL Database Updates: Deleting Data from the Database

A row of data is typically deleted from a database when the entity represented by the row disappears from the outside world. For example, in the sample database:

  • When a customer cancels an order, the corresponding row of the ORDERS table must be deleted.
  • When a salesperson leaves the company, the corresponding row of the SALESREPS table must be deleted.
  • When a sales office is closed, the corresponding row of the OFFICES table must be deleted. If the salespeople in the office are terminated, their rows should be deleted from the SALESREPS table as well. If they are reassigned, their REP_ OFFICE columns must be updated.

In each case, the row is deleted to maintain the database as an accurate model of the real world. The smallest unit of data that can be deleted from a relational database is a single row.

1. The delete Statement

The DELETE statement, shown in Figure 10-5, removes selected rows of data from a single table. The FROM clause specifies the target table containing the rows. The WHERE clause specifies which rows of the table are to be deleted.

Suppose Henry Jacobsen, the new salesperson hired earlier in this chapter, has just decided to leave the company. The DELETE statement that removes his row from the SALESREPS table is shown next.

Remove Henry Jacobsen from the database.

DELETE FROM SALESREPS

WHERE NAME = ‘Henry Jacobsen’

1 row deleted.

The WHERE clause in this example identifies a single row of the SALESREPS table, which SQL removes from the table. The WHERE clause should have a familiar appearance—it’s exactly the same WHERE clause that you would specify in a SELECT statement to retrieve the same row from the table. The search conditions that can be specified in the WHERE clause of the DELETE statement are the same ones available in the WHERE clause of the SELECT statement, as described in Chapters 6 and 9.

Recall that search conditions in the WHERE clause of a SELECT statement can specify a single row or an entire set of rows, depending on the specific search condition. The same is true of the WHERE clause in a DELETE statement. Suppose, for example, that Mr. Jacobsen’s customer, InterCorp (customer number 2126) has called to cancel all its orders. Here is the DELETE statement that removes the orders from the ORDERS table:

Remove all orders for InterCorp (customer number 2126).

DELETE FROM ORDERS

WHERE CUST = 2126

2 rows deleted.

In this case, the WHERE clause selects several rows of the ORDERS table, and SQL removes all of the selected rows from the table. Conceptually, SQL applies the WHERE clause to each row of the ORDERS table, deleting those where the search condition yields a TRUE result and retaining those where the search condition yields a FALSE or NULL result.

Because this type of DELETE statement searches through a table for the rows to be deleted, it is sometimes called a searched DELETE statement. This term is used to contrast it with another form of the DELETE statement, called the positioned DELETE statement, which always deletes a single row. The positioned DELETE statement applies only to programmatic SQL and is described in Chapter 17.

Here are some additional examples of searched DELETE statements:

Delete all orders placed before November 15,1989.

I  DELETE FROM ORDERS

WHERE ORDER_DATE < ’15-NOV-89′

5 rows deleted.

Delete all rows for customers served by Bill Adams, Mary Jones, or Dan Roberts (employee numbers 105,109, and 101).

DELETE FROM CUSTOMERS

WHERE CUST_REP IN (105, 109, 101)

7 rows deleted.

Delete all salespeople hired before July 1988 who have not yet been assigned a quota.

 DELETE FROM SALESREPS

WHERE HIRE_DATE < ’01-JUL-88′

AND QUOTA IS NULL

0 rows deleted.

2. Deleting All Rows

The WHERE clause in a DELETE statement is optional, but it is almost always present. If the WHERE clause is omitted from a DELETE statement, all rows of the target table are deleted, as in this example:

Delete all orders.

DELETE FROM ORDERS

30 rows deleted.

Although this DELETE statement produces an empty table, it does not erase the ORDERS table from the database. The definition of the ORDERS table and its columns is still stored in the database. The table still exists, and new rows can still be inserted into the ORDERS table with the INSERT statement. To erase the definition of the table from the database, the DROP TABLE statement (described in Chapter 13) must be used.

Because of the potential damage from such a DELETE statement, be careful to always specify a search condition, and be sure that it actually selects the rows you want. When using interactive SQL, it’s a good idea first to use the WHERE clause in a SELECT statement to display the selected rows. Make sure they are the ones you want to delete, and only then use the WHERE clause in a DELETE statement.

3. Delete with Subquery *

DELETE statements with simple search conditions, such as those in the previous examples, select rows for deletion based solely on the contents of the rows themselves. Sometimes the selection of rows must be made based on data from other tables. For example, suppose you want to delete all orders taken by Sue Smith. Without knowing her employee number, you can’t find the orders by consulting the ORDERS table alone. To find the orders, you could use a two-table query:

Find the orders taken by Sue Smith.

SELECT ORDER_NUM, AMOUNT
   FROM ORDERS, SALESREPS
WHERE REP = EMPL_NUM
   AND NAME = ‘Sue Smith’

ORDER_NUM    AMOUNT
———- ———–
112979      $15,000.00
113065      $2,130.00
112993      $1,896.00
113048      $3,750.00

But you can’t use a join in a DELETE statement. The parallel DELETE statement is illegal:

DELETE FROM ORDERS, SALESREPS

WHERE REP = EMPL_NUM

AND NAME = ‘Sue Smith’

Error: More than one table specified in FROM clause

The way to handle the request is with one of the subquery search conditions. Here is a valid form of the DELETE statement that handles the request:

Delete the orders taken by Sue Smith.

DELETE FROM ORDERS

WHERE REP = (SELECT EMPL_NUM FROM SALESREPS WHERE NAME = ‘Sue Smith’)

4 rows deleted.

The subquery finds the employee number for Sue Smith, and the WHERE clause then selects the orders with a matching value. As this example shows, subqueries can play an important role in the DELETE statement because they let you delete rows based on information in other tables. Here are two more examples of DELETE statements that use subquery search conditions:

Delete customers served by salespeople whose sales are less than 80 percent of quota.

DELETE FROM CUSTOMERS

WHERE CUST_REP IN (SELECT EMPL_NUM

FROM SALESREPS

WHERE SALES < (.8 * QUOTA))

2 rows deleted.

Delete any salesperson whose current orders total less than 2 percent of their quota.

DELETE FROM SALESREPS WHERE (.02 * QUOTA) > (SELECT SUM(AMOUNT)

FROM ORDERS

WHERE REP = EMPL NUM)

1 row deleted.

Subqueries in the WHERE clause can be nested just as they can be in the WHERE clause of the SELECT statement. They can also contain outer references to the target table of the DELETE statement. In this respect, the FROM clause of the DELETE statement functions like the FROM clause of the SELECT statement. Here is an example of a deletion request that requires a subquery with an outer reference:

Delete customers who have not ordered since November 10,1989.

DELETE FROM CUSTOMERS WHERE NOT EXISTS (SELECT *

FROM ORDERS

WHERE CUST = CUST_NUM

AND ORDER_DATE < ’10-NOV-89′)

16 rows deleted.

Conceptually, this DELETE statement operates by going through the CUSTOMERS table, row by row, and checking the search condition. For each customer, the subquery selects any orders placed by that customer before the cutoff date. The reference to the CUST_NUM column in the subquery is an outer reference to the customer number in the row of the CUSTOMERS table currently being checked by the DELETE statement. The subquery in this example is a correlated subquery, as described in Chapter 9.

Outer references will often be found in subqueries of a DELETE statement, because they implement the join between the table(s) in the subquery and the target table of the DELETE statement. In the SQL1 standard, a restriction on the use of subqueries in a DELETE statement prevents the target table from appearing in the FROM clause of a subquery or any of its subqueries at any level of nesting. This prevents the subqueries from referencing the target table (some of whose rows may already have been deleted), except for outer references to the row currently being tested by the DELETE statement’s search condition.

The SQL2 standard eliminates this restriction by specifying that the DELETE statement should treat such a subquery as applying to the entire target table, before any rows have been deleted. This places more overhead on the DBMS (which must handle the subquery processing and row deletion more carefully), but the behavior of the statement is well defined by the standard.

Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.

Leave a Reply

Your email address will not be published. Required fields are marked *