totn MariaDB

MariaDB: DELETE LIMIT Statement

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

Description

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

Syntax

The syntax for the DELETE LIMIT statement in MariaDB 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
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
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 MariaDB 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 MariaDB.

For example:

DELETE FROM sites
WHERE site_name = 'TechOnTheNet.com'
ORDER BY site_id DESC
LIMIT 3;

This DELETE LIMIT example would delete the first 3 records from the sites table where the site_name is 'TechOnTheNet.com. Note that the results are sorted by site_id in descending order so this means that the 3 largest site_id values will be deleted by the DELETE LIMIT statement.

If there are other records in the sites table that have a site_name of 'TechOnTheNet.com', they will not be deleted by the DELETE LIMIT statement in MariaDB.

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

DELETE FROM sites
WHERE site_name = 'TechOnTheNet.com'
ORDER BY site_id ASC
LIMIT 3;

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

Share on: