DELETE is a DML statement that
removes rows from a table.
DELETE statement can start with a
WITH clause to define common table
expressions accessible within the
DELETE. See Section 13.2.20, “WITH (Common Table Expressions)”.
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias] [PARTITION (partition_name [, partition_name] ...)] [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
DELETE statement deletes rows from
tbl_name and returns the number of
deleted rows. To check the number of deleted rows, call the
ROW_COUNT() function described in
Section 12.15, “Information Functions”.
The conditions in the optional
identify which rows to delete. With no
clause, all rows are deleted.
where_condition is an expression that
evaluates to true for each row to be deleted. It is specified as
described in Section 13.2.13, “SELECT Statement”.
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. These clauses apply to single-table
deletes, but not multi-table deletes.
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*]] ... FROM table_references [WHERE where_condition] DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*]] ... USING table_references [WHERE where_condition]
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
When you do not need to know the number of deleted rows, the
TRUNCATE TABLE statement is a
faster way to empty a table than a
DELETE statement with no
WHERE clause. Unlike
TRUNCATE TABLE cannot be used
within a transaction or if you have a lock on the table. See
Section 13.1.37, “TRUNCATE TABLE Statement” and
Section 13.3.6, “LOCK TABLES and UNLOCK TABLES Statements”.
The speed of delete operations may also be affected by factors discussed in Section 22.214.171.124, “Optimizing DELETE Statements”.
To ensure that a given
statement does not take too much time, the MySQL-specific
DELETE specifies the
maximum number of rows to be deleted. If the number of rows to
delete is larger than the limit, repeat the
DELETE statement until the number of affected
rows is less than the
You cannot delete from a table and select from the same table in a subquery.
Partitioned Table Support
DELETE supports explicit partition selection
PARTITION clause, which takes a list
of the comma-separated names of one or more partitions or
subpartitions (or both) from which to select rows to be dropped.
Partitions not included in the list are ignored. Given a
t with a partition named
p0, executing the statement
FROM t PARTITION (p0) has the same effect on the table
TABLE t TRUNCATE PARTITION (p0); in both cases, all rows
p0 are dropped.
PARTITION can be used along with a
WHERE condition, in which case the condition is
tested only on rows in the listed partitions. For example,
DELETE FROM t PARTITION (p0) WHERE c < 5
deletes rows only from partition
p0 for which
c < 5 is true; rows in any
other partitions are not checked and thus not affected by the
PARTITION clause can also be used in
DELETE statements. You can use
up to one such option per table named in the
For more information and examples, see Section 24.5, “Partition Selection”.
If you delete the row containing the maximum value for an
AUTO_INCREMENT column, the value is not reused
table. If you delete all rows in the table with
FROM (without a
WHERE clause) in
autocommit mode, the sequence
starts over for all storage engines except
MyISAM. There are
some exceptions to this behavior for
tables, as discussed in
Section 126.96.36.199, “AUTO_INCREMENT Handling in InnoDB”.
MyISAM tables, you can specify an
AUTO_INCREMENT secondary column in a
multiple-column key. In this case, reuse of values deleted from
the top of the sequence occurs even for
tables. See Section 3.6.9, “Using AUTO_INCREMENT”.
DELETE statement supports the
If you specify the
LOW_PRIORITYmodifier, the server delays execution of the
DELETEuntil no other clients are reading from the table. This affects only storage engines that use only table-level locking (such as
MyISAMtables, if you use the
QUICKmodifier, the storage engine does not merge index leaves during delete, which may speed up some kinds of delete operations.
IGNOREmodifier causes MySQL to ignore ignorable errors during the process of deleting rows. (Errors encountered during the parsing stage are processed in the usual manner.) Errors that are ignored due to the use of
IGNOREare returned as warnings. For more information, see The Effect of IGNORE on Statement Execution.
Order of Deletion
DELETE statement includes an
ORDER BY clause, rows are deleted in the order
specified by the clause. This is useful primarily in conjunction
LIMIT. For example, the following
statement finds rows matching the
sorts them by
timestamp_column, and deletes the
first (oldest) one:
DELETE FROM somelog WHERE user = 'jcole' ORDER BY timestamp_column LIMIT 1;
ORDER BY also helps to delete rows in an order
required to avoid referential integrity violations.
If you are deleting many rows from a large table, you may exceed
the lock table size for an
InnoDB table. To
avoid this problem, or simply to minimize the time that the table
remains locked, the following strategy (which does not use
DELETE at all) might be helpful:
Select the rows not to be deleted into an empty table that has the same structure as the original table:
INSERT INTO t_copy SELECT * FROM t WHERE ... ;
RENAME TABLEto atomically move the original table out of the way and rename the copy to the original name:
RENAME TABLE t TO t_old, t_copy TO t;
Drop the original table:
DROP TABLE t_old;
No other sessions can access the tables involved while
RENAME TABLE executes, so the
rename operation is not subject to concurrency problems. See
Section 13.1.36, “RENAME TABLE Statement”.
MyISAM tables, deleted rows are maintained
in a linked list and subsequent
INSERT operations reuse old row
positions. To reclaim unused space and reduce file sizes, use the
OPTIMIZE TABLE statement or the
myisamchk utility to reorganize tables.
OPTIMIZE TABLE is easier to use,
but myisamchk is faster. See
Section 188.8.131.52, “OPTIMIZE TABLE Statement”, and Section 4.6.4, “myisamchk — MyISAM Table-Maintenance Utility”.
QUICK modifier affects whether index leaves
are merged for delete operations.
is most useful for applications where index values for deleted
rows are replaced by similar index values from rows inserted
later. In this case, the holes left by deleted values are reused.
DELETE QUICK is not useful when deleted values
lead to underfilled index blocks spanning a range of index values
for which new inserts occur again. In this case, use of
QUICK can lead to wasted space in the index
that remains unreclaimed. Here is an example of such a scenario:
Create a table that contains an indexed
Insert many rows into the table. Each insert results in an index value that is added to the high end of the index.
Delete a block of rows at the low end of the column range using
In this scenario, the index blocks associated with the deleted
index values become underfilled but are not merged with other
index blocks due to the use of
remain underfilled when new inserts occur, because new rows do not
have index values in the deleted range. Furthermore, they remain
underfilled even if you later use
QUICK, unless some of the deleted index values
happen to lie in index blocks within or adjacent to the
underfilled blocks. To reclaim unused index space under these
If you are going to delete many rows from a table, it might be
faster to use
DELETE QUICK followed by
OPTIMIZE TABLE. This rebuilds the
index rather than performing many index block merge operations.
You can specify multiple tables in a
DELETE statement to delete rows
from one or more tables depending on the condition in the
WHERE clause. You cannot use
LIMIT in a multiple-table
table_references clause lists the
tables involved in the join, as described in
Section 184.108.40.206, “JOIN Clause”.
For the first multiple-table syntax, only matching rows from the
tables listed before the
FROM clause are
deleted. For the second multiple-table syntax, only matching rows
from the tables listed in the
USING clause) are deleted. The
effect is that you can delete rows from many tables at the same
time and have additional tables that are used only for searching:
DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;
DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;
These statements use all three tables when searching for rows to
delete, but delete matching rows only from tables
The preceding examples use
INNER JOIN, but
can use other types of join permitted in
SELECT statements, such as
LEFT JOIN. For example, to delete rows that
t1 that have no match in
t2, use a
DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
The syntax permits
.* after each
tbl_name for compatibility with
If you use a multiple-table
InnoDB tables for which
there are foreign key constraints, the MySQL optimizer might
process tables in an order that differs from that of their
parent/child relationship. In this case, the statement fails and
rolls back. Instead, you should delete from a single table and
rely on the
ON DELETE capabilities that
InnoDB provides to cause the other tables to be
If you declare an alias for a table, you must use the alias when referring to the table:
DELETE t1 FROM test AS t1, test2 WHERE ...
Table aliases in a multiple-table
DELETE should be declared only in
table_references part of the
statement. Elsewhere, alias references are permitted but not alias
DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id; DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id;
DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2 WHERE a1.id=a2.id; DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2 WHERE a1.id=a2.id;
Table aliases are also supported for single-table
DELETE statements beginning with MySQL 8.0.16.
(Bug #89410,Bug #27455809)