Delete selected rows

You can also choose rows that are based on nonindexed columns, as the following example shows: 
DELETE FROM customer WHERE company = 'Druid Cyclery';

Because the column that is tested does not have a unique constraint, this statement might delete more than one row. (Druid Cyclery might have two stores, both with the same name but different customer numbers.)

To find out how many rows a DELETE statement affects, select the count of qualifying rows from the customer table for Druid Cyclery. 
SELECT COUNT(*) FROM customer WHERE company = 'Druid Cyclery';

You can also select the rows and display them to ensure that they are the ones you want to delete.

Using a SELECT statement as a test is only an approximation, however, when the database is available to multiple users concurrently. Between the time you execute the SELECT statement and the subsequent DELETE statement, other users could have modified the table and changed the result. In this example, another user might perform the following actions:
  • Insert a new row for another customer named Druid Cyclery
  • Delete one or more of the Druid Cyclery rows before you insert the new row
  • Update a Druid Cyclery row to have a new company name, or update some other customer to have the name Druid Cyclery.

Although it is not likely that other users would do these things in that brief interval, the possibility does exist. This same problem affects the UPDATE statement. Ways of addressing this problem are discussed under Concurrency and locks, and in greater detail in Programming for a multiuser environment.

Another problem you might encounter is a hardware or software failure before the statement finishes. In this case, the database might have deleted no rows, some rows, or all specified rows. The state of the database is unknown, which is undesirable. To prevent this situation, use transaction logging, as Interrupted modifications discusses.