MySQL: DELETE LIMIT Statement

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

Description

The MySQL DELETE LIMIT statement is used to delete records from a table in MySQL and limit the number of records deleted based on a limit value.

Syntax

The syntax for the DELETE LIMIT statement in MySQL is:

DELETE FROM table
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
LIMIT row_count;

Parameters or Arguments

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.

ORDER BY expression

Optional. It is used in the DELETE LIMIT statement so that you can order the results and target those records that you wish to delete.

LIMIT row_count

It specifies a limited number of rows in the result set to delete based on row_count. For example, LIMIT 10 would delete the first 10 rows matching the delete criteria. This is where sort order matters so be sure to use an ORDER BY clause appropriately.

Note

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

Example

Let's look at how to use a DELETE statement with a LIMIT clause in MySQL.

For example:

DELETE FROM contacts
WHERE website = 'AODBA.com'
ORDER BY contact_id DESC
LIMIT 2;

This DELETE LIMIT example would delete the first 2 records from the contacts table where the website is 'AODBA.com'. Note that the results are sorted by contact_id in descending order so this means that the 2 largest contact_id values will be deleted by the DELETE LIMIT statement.

If there are other records in the contacts table that have a website value of 'AODBA.com', they will not be deleted by the DELETE LIMIT statement in MySQL.

If we wanted to delete the smallest contact_id values instead of the largest two, we could change the sort order as follows:

DELETE FROM contacts
WHERE website = 'AODBA.com'
ORDER BY contact_id ASC
LIMIT 2;

Now the results would be sorted by contact_id in ascending order, so the first two smallest contact_id records that have a website of 'AODBA.com' would be deleted by this DELETE LIMIT statement. No other records would be affected.