The DELETE Statement

The DELETE statement is used to remove data rows from a table. Its syntax is similar to a SELECT statement: You supply a table name after the keyword FROM and use a WHERE clause to filter the rows that are to be deleted.

Deleting

Without a WHERE clause, DELETE deletes every rows from the given table. Unless this is the result you want, always make sure that you include a WHERE clause in a DELETE statement.

To delete only a single row from a table, you should ensure that the WHERE clause will match only that row. Usually, you should check the value of the table's PRIMARY KEY column to ensure that an exact match is found.

The following example deletes one of the new products that you inserted in the previous section:

 mysql> DELETE FROM products     -> WHERE product_code = 'NEWPROD'; Query OK, 1 row affected (0.00 sec) 

The response from the DELETE command indicates the number of rows that were removed. The following example deletes several rows from the customer_contacts table using a WHERE clause that finds all the customers with a given name. These rows will be the ones inserted in the section "Using a Query to Insert Rows."

 mysql> DELETE FROM customer_contacts     -> WHERE first_name = 'Joe'     -> AND last_name = 'Soap'; Query OK, 6 rows affected (0.00 sec) 

A DELETE cannot be undone unless it is part of a transaction that is rolled back. You will learn about this feature in the "Transactions in MySQL" section later in this lesson.