Syntax
------

Single-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] 
 FROM tbl_name [PARTITION (partition_list)]
 [FOR PORTION OF period FROM expr1 TO expr2]
 [WHERE where_condition]
 [ORDER BY ...]
 [LIMIT row_count]
 [RETURNING select_expr
  [, select_expr ...]]

Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
  tbl_name[.*] [, tbl_name[.*]] ...
  FROM table_references
  [WHERE where_condition]

Or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
  FROM tbl_name[.*] [, tbl_name[.*]] ...
  USING table_references
  [WHERE where_condition]

Trimming history:

DELETE HISTORY
 FROM tbl_name [PARTITION (partition_list)]
 [BEFORE SYSTEM_TIME [TIMESTAMP|TRANSACTION] expression]

Description
-----------

+---------------------------+------------------------------------------------+
| Option                    | Description                                    |
+---------------------------+------------------------------------------------+
| LOW_PRIORITY              | Wait until all SELECT's are done before        |
|                           | starting the statement. Used with storage      |
|                           | engines that uses table locking (MyISAM, Aria  |
|                           | etc). See HIGH_PRIORITY and LOW_PRIORITY       |
|                           | clauses for details.                           |
+---------------------------+------------------------------------------------+
| QUICK                     | Signal the storage engine that it should       |
|                           | expect that a lot of rows are deleted. The     |
|                           | storage engine engine can do things to speed   |
|                           | up the DELETE like ignoring merging of data    |
|                           | blocks until all rows are deleted from the     |
|                           | block (instead of when a block is half full).  |
|                           | This speeds up things at the expanse of lost   |
|                           | space in data blocks. At least MyISAM and      |
|                           | Aria support this feature.                     |
+---------------------------+------------------------------------------------+
| IGNORE                    | Don't stop the query even if a not-critical    |
|                           | error occurs (like data overflow). See How     |
|                           | IGNORE works for a full description.           |
+---------------------------+------------------------------------------------+

For the single-table syntax, the DELETE statement deletes rows from tbl_name
and returns a count of the number of deleted rows. This count can be obtained
by calling the ROW_COUNT() function. The WHERE clause, if given, specifies the
conditions that identify which rows to delete. With no WHERE clause, all rows
are deleted. If the ORDER BY clause is specified, the rows are deleted in the
order that is specified. The LIMIT clause places a limit on the number of rows
that can be deleted.

For the multiple-table syntax, DELETE deletes from each tbl_name the rows that
satisfy the conditions. In this case, ORDER BY and LIMIT> cannot be used. A
DELETE can also reference tables which are located in different databases; see
Identifier Qualifiers for the syntax.

where_condition is an expression that evaluates to true for each row to be
deleted. It is specified as described in SELECT.

Currently, you cannot delete from a table and select from the same table in a
subquery.

You need the DELETE privilege on a table to delete rows from it. You need only
the SELECT privilege for any columns that are only read, such as those named
in the WHERE clause. See GRANT.

As stated, a DELETE statement with no WHERE clause deletes all rows. A faster
way to do this, when you do not need to know the number of deleted rows, is to
use TRUNCATE TABLE. However, within a transaction or if you have a lock on the
table, TRUNCATE TABLE cannot be used whereas DELETE can. See TRUNCATE TABLE,
and LOCK.

PARTITION
---------

See Partition Pruning and Selection for details.

FOR PORTION OF
--------------

MariaDB starting with 10.4.3
----------------------------
See Application Time Periods - Deletion by Portion.

RETURNING
---------

It is possible to return a resultset of the deleted rows for a single table to
the client by using the syntax DELETE ... RETURNING select_expr [,
select_expr2 ...]]

Any of SQL expression that can be calculated from a single row fields is
allowed. Subqueries are allowed. The AS keyword is allowed, so it is possible
to use aliases.

The use of aggregate functions is not allowed. RETURNING cannot be used in
multi-table DELETEs.

MariaDB starting with 10.3.1
----------------------------

Same Source and Target Table
----------------------------

Until MariaDB 10.3.1, deleting from a table with the same source and target
was not possible. From MariaDB 10.3.1, this is now possible. For example:

DELETE FROM t1 WHERE c1 IN (SELECT b.c1 FROM t1 b WHERE b.c2=0);

MariaDB starting with 10.3.4
----------------------------

DELETE HISTORY
--------------

One can use DELETE HISTORY to delete historical information from
System-versioned tables.

Examples
--------

How to use the ORDER BY and LIMIT clauses:

DELETE FROM page_hit ORDER BY timestamp LIMIT 1000000;

How to use the RETURNING clause:

DELETE FROM t RETURNING f1;
+------+
| f1   |
+------+
|    5 |
|   50 |
|  500 |
+------+

The following statement joins two tables: one is only used to satisfy a WHERE
condition, but no row is deleted from it; rows from the other table are
deleted, instead.

DELETE post FROM blog INNER JOIN post WHERE blog.id = post.blog_id;

Deleting from the Same Source and Target
----------------------------------------

CREATE TABLE t1 (c1 INT, c2 INT);
DELETE FROM t1 WHERE c1 IN (SELECT b.c1 FROM t1 b WHERE b.c2=0);

Until MariaDB 10.3.1, this returned:

ERROR 1093 (HY000): Table 't1' is specified twice, both as a target for
'DELETE' 
 and as a separate source for

From MariaDB 10.3.1:

Query OK, 0 rows affected (0.00 sec)

URL: https://mariadb.com/kb/en/delete/