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.
The DELETE Statement
The DELETE statement, 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 that Henry Jacobsen, the new salesperson hired earlier in this chapter, has just decided to leave the company. Here is the DELETE statement that removes his row from the SALESREPS table:
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.
