MySQL: DELETE Statement

This tutorial explains how to use the MySQL DELETE statement with syntax and examples.

Description

The MySQL DELETE statement is used to delete a single record or multiple records from a table in MySQL.

Syntax

In its simplest form, the syntax for the DELETE statement in MySQL is:

DELETE FROM table
[WHERE conditions];

However, the full syntax for the DELETE statement in MySQL is:

DELETE [ LOW_PRIORITY ] [ QUICK ] [ IGNORE ] FROM table
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
[LIMIT number_rows];

Parameters or Arguments

LOW_PRIORITY

Optional. If LOW_PRIORITY is provided, the delete will be delayed until there are no processes reading from the table. LOW_PRIORITY may be used with MyISAM, MEMORY and MERGE tables that use table-level locking.

QUICK

Optional. If QUICK is provided, ndex leaves are not merged during the delete making the deletion faster for MyISAM tables.

IGNORE

Optional. If IGNORE is provided, all errors encountered during the delete are ignored. IGNORE was introduced in MySQL 4.1.1.

table

The table that you wish to delete records from.

WHERE conditions

Optional. The conditions that must be met for the records to be deleted. If no conditions are provided, then all records from the table will be deleted.

ORDER BY expression

Optional. It may be used in combination with LIMIT to sort the records appropriately when limiting the number of records to be deleted.

LIMIT

Optional. If LIMIT is provided, it controls the maximum number of records to delete from the table. At most, the number of records specified by number_rows will be deleted from the table.

Note

• You do not need to list fields in the MySQL DELETE statement since you are deleting the entire row from the table.

Example - With One condition

Let's look at a simple MySQL DELETE query example, where we just have one condition in the DELETE statement.

For example:

DELETE FROM contacts
WHERE last_name = 'Johnson';

This MySQL DELETE example would delete all records from the contacts table where the last_name is Johnson.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by calling the mysql_info function or by running the following MySQL SELECT statement before performing the delete.

SELECT count(*)
FROM contacts
WHERE last_name = 'Johnson';

Example - With Two conditions

Let's look at a MySQL DELETE example, where we just have two conditions in the DELETE statement.

For example:

DELETE FROM contacts
WHERE last_name = 'Johnson'
AND contact_id  1000;

This MySQL DELETE example would delete all records from the contacts table where the last_name is 'Johnson' and the customer_id is less than 1000.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by calling the mysql_info function or by running the following MySQL SELECT statement before performing the delete.

SELECT count(*)
FROM contacts
WHERE last_name = 'Johnson'
AND contact_id  1000;

Example - With LIMIT modifier

Let's look at a MySQL DELETE example where we use the LIMIT modifier to control the number of records deleted.

For example:

DELETE FROM contacts
WHERE last_name = 'Johnson'
ORDER BY contact_id DESC
LIMIT 1;

This MySQL DELETE example would delete one record from the contacts table (as specified by LIMIT 1) where the last_name is 'Johnson'. The DELETE is sorted in descending order by contact_id, so only the record with the largest contact_id whose last_name is 'Johnson' would be deleted from table. All other records in the contacts table with the last_name of 'Johnson' would remain in the table.

If you wished to instead delete the smallest contact_id whose last_name is 'Johnson', you could rewrite the DELETE statement as follows:

DELETE FROM contacts
WHERE last_name = 'Johnson'
ORDER BY contact_id ASC
LIMIT 1;

Or you could delete the last record in the contacts table with the following DELETE statement (assuming that the contact_id is a sequential number):

DELETE FROM contacts
ORDER BY contact_id DESC
LIMIT 1;

Example - Using EXISTS Condition

You can also perform more complicated deletes.

You may wish to delete records in one table based on values in another table. Since you can't list more than one table in the MySQL FROM clause when you are performing a delete, you can use the MySQL EXISTS clause.

For example:

DELETE FROM suppliers
WHERE EXISTS
  ( SELECT *
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id
    AND customer_id > 500 );

This MySQL DELETE example would delete all records in the suppliers table where there is a record in the customers table whose customer_id is greater than 500, and the customer_id matches the supplier_id.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by calling the mysql_info function or by running the following MySQL SELECT statement before performing the delete.

SELECT COUNT(*) FROM suppliers
WHERE EXISTS
  ( SELECT *
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id
    AND customer_id > 500 );