DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM
[ORDER BY ...]
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
For the single-table syntax, the
statement deletes rows from
and returns the number of rows deleted. 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,
tbl_name the rows that
satisfy the conditions. In this case,
LIMIT cannot be used.
where_condition is an expression that
evaluates to true for each row to be deleted. It is specified as
described in Section 13.2.7, “
As stated, a
DELETE statement with no
WHERE clause deletes all rows. A faster way
to do this, when you do not want to know the number of deleted
rows, is to use
TRUNCATE TABLE. See
Section 13.2.9, “
If you delete the row containing the maximum value for an
AUTO_INCREMENT column, the value is reused
later for a
BDB table, but not for a
InnoDB table. If
you delete all rows in the table with
WHERE clause) in
AUTOCOMMIT mode, the sequence starts over for
all storage engines except
MyISAM. There are some exceptions to this
InnoDB tables, as discussed in
Section 18.104.22.168, “How
AUTO_INCREMENT Columns Work in
you can specify an
column in a multiple-column key. In this case, reuse of values
deleted from the top of the sequence occurs even for
MyISAM tables. See
Section 3.6.9, “Using
DELETE statement supports the following
If you specify
LOW_PRIORITY, the server
delays execution of the
DELETE until no
other clients are reading from the table.
MyISAM tables, if you use the
QUICK keyword, the storage engine does
not merge index leaves during delete, which may speed up
some kinds of delete operations.
IGNORE keyword causes MySQL to ignore
all 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
OPTION are returned as warnings.
The speed of delete operations may also be affected by factors
discussed in Section 7.2.18, “Speed of
MyISAM tables, deleted rows are maintained
in a linked list and subsequent
operations reuse old row positions. To reclaim unused space and
reduce file sizes, use the
statement or the myisamchk utility to
OPTIMIZE TABLE is easier,
but myisamchk is faster. See
Section 22.214.171.124, “
OPTIMIZE TABLE Syntax”, and
Section 8.2, “myisamchk —
MyISAM Table-Maintenance Utility”.
QUICK modifier affects whether index
leaves are merged for delete operations.
QUICK 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,
QUICK can lead to wasted space in the
index that remains unreclaimed. Here is an example of such a
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
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 does
not have index values in the deleted range. Furthermore, they
remain underfilled even if you later use
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 circumstances, use
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.
DELETE tells the server the maximum number of
rows to be deleted before control is returned to the client.
This can be used to ensure that a given
DELETE statement does not take too much time.
You can simply repeat the
until the number of affected rows is less than the
DELETE statement includes an
ORDER BY clause, the rows are deleted in the
order specified by the clause. This is really useful only in
LIMIT. For example, the
following statement finds rows matching the
WHERE clause, sorts them by
timestamp_column, and deletes the first
DELETE FROM somelog WHERE user = 'jcole'
ORDER BY timestamp_column LIMIT 1;
You can specify multiple tables in a
statement to delete rows from one or more tables depending on
the particular condition in the
However, you cannot use
ORDER BY or
LIMIT in a multiple-table
table_references clause lists the
tables involved in the join. Its syntax is described in
Section 126.96.36.199, “
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
clause (before 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
DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
DELETE FROM t1, t2 USING t1, t2, 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 show inner joins that use the comma
operator, but multiple-table
statements can use any type of join allowed in
SELECT statements, such as
The syntax allows
.* after the table names
for compatibility with Access.
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 modified accordingly.
Note: If you provide an alias
for a table, you must use the alias when referring to the table:
DELETE t1 FROM test AS t1, test2 WHERE ...
Cross-database deletes are supported for multiple-table deletes,
but in this case, you must refer to the tables without using
aliases. For example:
DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE ...
Currently, you cannot delete from a table and select from the
same table in a subquery.