Syntax
------ 
Single-table syntax:
 
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] 
 FROM tbl_name [PARTITION (partition_list)]
 [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.
 
The PARTITION clause was introduced in MariaDB 10.0. See
Partition Pruning and Selection for details.
 
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.
 
From MariaDB 10.0.5, 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.
 
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);
 
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/