Using the DELETE Command

Using the DELETE Command

The basic DELETE syntax is

 DELETE FROM table_name [WHERE some_condition_is_true] [LIMIT rows] 

Notice there is no column specification used in the DELETE commandwhen you use DELETE, the entire record is removed. You might recall the fiasco earlier in this chapter, regarding grapes in the fruit table, when updating a table without specifying a condition caused all records to be updated. You must be similarly careful when using DELETE.

Assuming the structure and data in a table called fruit:

 mysql> select * from fruit; +----+------------+--------+ | id | fruit_name | status | +----+------------+--------+ |  1 | apple      | ripe   | |  2 | pear       | rotten | |  3 | banana     | ripe   | |  4 | grape      | rotten | +----+------------+--------+ 4 rows in set (0.00 sec) 

This statement will remove all records in the table:

 mysql> delete from fruit; Query OK, 0 rows affected (0.00 sec) 

You can always verify the deletion by attempting to SELECT data from the table. If you issued this command after removing all the records:

 mysql> select * from fruit; Empty set (0.00 sec) 

You would see that all your fruit is gone.

Conditional DELETE

A conditional DELETE statement, just like a conditional SELECT or UPDATE statement, means you are using WHERE clauses to match specific records. You have the full range of comparison and logical operators available to you, so you can pick and choose which records you want to delete.

A prime example would be to remove all records for rotten fruit from the fruit table:

 mysql> delete from fruit where status = 'rotten'; Query OK, 2 rows affected (0.00 sec) 

Two records were deleted, and only ripe fruit remains:

 mysql> select * from fruit; +----+------------+--------+ | id | fruit_name | status | +----+------------+--------+ |  1 | apple      | ripe   | |  3 | banana     | ripe   | +----+------------+--------+ 2 rows in set (0.00 sec) 

Since MySQL 4.0.x, users can also use ORDER BY clauses in your DELETE statements. Take a look at the basic DELETE syntax with the ORDER BY clause added to its structure:

 DELETE FROM table_name [WHERE some_condition_is_true] [ORDER BY some_column [ASC | DESC]] [LIMIT rows] 

At first glance, you might wonder, "Why does it matter in what order I delete records?" The ORDER BY clause isn't for the deletion order, it's for the sorting order of records.

In this example, a table called access_log shows access time and username:

 mysql> select * from access_log; +----+---------------------+----------+ | id | date_accessed       | username | +----+---------------------+----------+ |  1 | 2001-11-06 06:09:13 | johndoe  | |  2 | 2001-11-06 06:09:22 | janedoe  | |  3 | 2001-11-06 06:09:39 | jsmith   | |  4 | 2001-11-06 06:09:44 | mikew    | +----+---------------------+----------+ 4 rows in set (0.00 sec) 

To remove the oldest record, first use ORDER BY to sort the results appropriately, and then use LIMIT to remove just one record:

 mysql> delete from access_log order by date_accessed desc limit 1; Query OK, 1 row affected (0.01 sec) 

Select the record from access_log and verify that only three records exist:

 mysql> select * from access_log; +----+---------------------+----------+ | id | date_accessed       | username | +----+---------------------+----------+ |  2 | 2001-11-06 06:09:22 | janedoe  | |  3 | 2001-11-06 06:09:39 | jsmith   | |  4 | 2001-11-06 06:09:44 | mikew    | +----+---------------------+----------+ 3 rows in set (0.00 sec)