This tutorial explains how to use the DELETE LIMIT statement in MySQL with syntax and examples.
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.
The syntax for the DELETE LIMIT statement in MySQL is:
Parameters or Arguments
The table that you wish to delete records from.
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.
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.
You do not need to list fields in the MySQL DELETE LIMIT statement since you are deleting the entire row from the table.
Let's look at how to use a DELETE statement with a LIMIT clause in MySQL.
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:
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.