DELETE

Syntax:
DELETE is a DML statement that removes rows from a table.

A DELETE statement can start with a WITH clause to define common table
expressions accessible within the DELETE. See
https://dev.mysql.com/doc/refman/8.0/en/with.html.

Single-Table Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
[PARTITION (partition_name [, partition_name] ...)]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

The 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
https://dev.mysql.com/doc/refman/8.0/en/information-functions.html.

Main Clauses

The conditions in the optional WHERE clause identify which rows to
delete. With no WHERE 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
https://dev.mysql.com/doc/refman/8.0/en/select.html.

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. These clauses apply to single-table deletes,
but not multi-table deletes.

Multiple-Table Syntax

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]

Privileges

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.

Performance

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 DELETE, TRUNCATE TABLE cannot be
used within a transaction or if you have a lock on the table. See [HELP
TRUNCATE TABLE] and [HELP LOCK TABLES].

The speed of delete operations may also be affected by factors
discussed in
https://dev.mysql.com/doc/refman/8.0/en/delete-optimization.html.

To ensure that a given DELETE statement does not take too much time,
the MySQL-specific LIMIT row_count clause for 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 LIMIT value.

Subqueries

You cannot delete from a table and select from the same table in a
subquery.

Partitioned Table Support

DELETE supports explicit partition selection using the PARTITION
option, 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
partitioned table t with a partition named p0, executing the statement
DELETE FROM t PARTITION (p0) has the same effect on the table as
executing ALTER TABLE t TRUNCATE PARTITION (p0); in both cases, all
rows in partition 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 the condition c < 5 is true; rows in any other
partitions are not checked and thus not affected by the DELETE.

The PARTITION option can also be used in multiple-table DELETE
statements. You can use up to one such option per table named in the
FROM option.

For more information and examples, see
https://dev.mysql.com/doc/refman/8.0/en/partitioning-selection.html.

URL: https://dev.mysql.com/doc/refman/8.0/en/delete.html