MariaDB: DELETE Statement

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

Description

The MariaDB DELETE statement is used to delete one or more records from a table in MariaDB.

Syntax

The syntax for the DELETE statement in MariaDB is:

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

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. 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 number_rows

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 MariaDB DELETE statement since you are deleting the entire row from the table.

Example - With One condition

Let's look at how to use the DELETE statement with one condition in MariaDB.

For example:

DELETE FROM sites
WHERE site_name = 'TechOnTheNet.com';

This MariaDB DELETE example would delete all records from the sites table where the site_name is 'TechOnTheNet.com'.

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 running the following SELECT statement before performing the delete.

SELECT COUNT(*)
FROM sites
WHERE site_name = 'TechOnTheNet.com';

Example - With Multiple conditions

Next, let's look at how to use the DELETE statement with multiple conditions in MariaDB.

For example:

DELETE FROM sites
WHERE site_name = 'TechOnTheNet.com'
AND site_id >= 65;

This DELETE statement example would delete all records from the sites table where the site_name is 'TechOnTheNet.com' and the site_id is greater than or equal to 65.

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 running the following SELECT statement before performing the delete.

SELECT COUNT(*)
FROM sites
WHERE site_name = 'TechOnTheNet.com'
AND site_id >= 65;

Example - With LIMIT modifier

Let's look at how to use the DELETE statement with the LIMIT modifier in MariaDB. The LIMIT modifier is used to control the number of records deleted.

For example:

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

This MariaDB DELETE example would delete one record from the sites table (as specified by LIMIT 3) where the site_name is 'TechOnTheNet.com'. The DELETE is sorted in ascending order by site_id, so only the three records with the smallest site_id values whose site_name is 'TechOnTheNet.com' would be deleted from table. All other records in the sites table with the site_name of 'TechOnTheNet.com' would remain in the table.

If you wished to instead delete the largest three site_id values whose site_name is 'TechOnTheNet.com', you could rewrite the DELETE statement as follows:

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

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 MariaDB FROM clause when you are performing a delete, you can use the EXISTS clause.

For example:

DELETE FROM sites
WHERE EXISTS
  ( SELECT *
    FROM pages
    WHERE pages.site_id = sites.site_id
    AND site_id <= 45 );

This DELETE example would delete all records in the sites table where there is a record in the pages table whose site_id is less than or equal to 45, and the site_id matches between the pages and sites tables.

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 running the following SELECT statement before performing the delete.

SELECT COUNT(*) FROM sites
WHERE EXISTS
  ( SELECT *
    FROM pages
    WHERE pages.site_id = sites.site_id
    AND site_id <= 45 );