Deleting Rows with DELETE

The final thing we'll look at today is the DELETE statement. Quite simply, DELETE removes rows of data from a table.

Here is its syntax:

DELETE FROM table_name
 [WHERE where_definition]
 [ORDER BY column_list]
 [LIMIT num_rows]

You should see immediately several similarities with the SELECT query (which is why we're looking at it now!).

For a start, there's a WHERE clause, which restricts the delete action to just those rows meeting the condition. This works exactly the same as in a SELECT statement.

Occasionally, you may also use a LIMIT clause, and if so, you may also use an ORDER BY clause. (ORDER BY has no effect without LIMIT.) You may choose a limit of 1 for safety (so that you can't delete everything by mistake), or you may find a reason for deleting num_rows rows of data.

Here's a simple example:

mysql> DELETE FROM child_products WHERE id=105;
Query OK, 1 row affected (0.01 sec)

In this example, the product with id 105 is deleted.

Here's another example:

mysql> DELETE FROM child_products WHERE name LIKE 'toy%';
Query OK, 1 row affected (0.00 sec)

Now all products are deleted if their name starts with the word toy.

Finally, to empty a table just do this:

mysql> DELETE FROM child_products;
Query OK, 3 rows affected (0.00 sec)

In this case, every remaining row is deleted from the table. Take care with DELETE!