# DELETE ## Syntax {% hint style="info" %} For the CTE[^1] syntax, available from MariaDB 12.3, see [here](#cte-syntax). {% endhint %} Single-table syntax: ```sql DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [PARTITION (partition_list)] [FOR PORTION OF PERIOD FROM expr1 TO expr2] [AS alias] -- from MariaDB 11.6 [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] [RETURNING select_expr [, select_expr ...]] ``` Multiple-table syntax: ```sql DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*]] ... FROM table_references [WHERE where_condition] ``` Or: ```sql DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*]] ... USING table_references [WHERE where_condition] ``` Trimming history: ```sql DELETE HISTORY FROM tbl_name [PARTITION (partition_list)] [BEFORE SYSTEM_TIME [TIMESTAMP|TRANSACTION] expression] ``` ### CTE Syntax {% hint style="info" %} This syntax is available from MariaDB 12.3. {% endhint %} ```sql WITH [RECURSIVE] table_reference [(columns_list)] AS ( SELECT ... ) DELETE FROM non_cte_table expression ``` * `non_cte_table` is a table not defined by a CTE (common table expression). * `expression` is a `WHERE` clause or a `USING`/`WHERE` clause. * Supporting CTEs with `DELETE` is an extension of the SQL standard, similar to how MySQL does it. * With `DELETE`, CTEs are read-only, like other derived tables – you cannot delete rows from tables in the CTE expression. * For use cases, see the CTE examples. ## Description | Option | Description | | ------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | LOW\_PRIORITY | Wait until all `SELECT` statement are done before starting the statement. Used with storage engines that uses table locking (MyISAM, Aria etc). See [HIGH\_PRIORITY and LOW\_PRIORITY clauses](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/high_priority-and-low_priority) for details. | | QUICK | Signal the storage engine that it should expect that a lot of rows are deleted. The storage 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](https://mariadb.com/docs/server/server-usage/storage-engines/myisam-storage-engine) and [Aria](https://mariadb.com/docs/server/server-usage/storage-engines/aria) support this feature. | | IGNORE | Don't stop the query even if a not-critical error occurs (like data overflow). See [How IGNORE works](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/ignore) 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()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/information-functions/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](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by) clause is specified, the rows are deleted in the order that is specified. The [LIMIT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/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](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by) and [LIMIT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/limit) cannot be used. A `DELETE` can also reference tables which are located in different databases; see [Identifier Qualifiers](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/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](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select). {% hint style="info" %} You cannot delete from a table and select from the same table in a subquery. {% endhint %} 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](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/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](https://mariadb.com/docs/server/reference/sql-statements/table-statements/truncate-table), and [LOCK](https://mariadb.com/docs/server/reference/sql-statements/transactions/lock-tables). ### AS {% tabs %} {% tab title="Current" %} Single-table `DELETE` statements support aliases. For example: ```sql CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (1), (2); DELETE FROM t1 AS a1 WHERE a1.c1 = 2; ``` {% endtab %} {% tab title="< 11.6" %} Single-table `DELETE` statements do **not** support aliases. {% endtab %} {% endtabs %} ### PARTITION See [Partition Pruning and Selection](https://mariadb.com/docs/server/server-usage/partitioning-tables/partition-pruning-and-selection) for details. ### FOR PORTION OF See [Application Time Periods - Deletion by Portion](https://mariadb.com/docs/server/sql-structure/temporal-tables/application-time-periods#deletion-by-portion). ### RETURNING It is possible to return a result set 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 `DELETE` statements. ### Same Source and Target Table It is possible to delete from a table with the same source and target. For example: ```sql DELETE FROM t1 WHERE c1 IN (SELECT b.c1 FROM t1 b WHERE b.c2=0); ``` ### DELETE HISTORY You can use `DELETE HISTORY` to delete historical information from [System-versioned tables](https://mariadb.com/docs/server/reference/sql-structure/temporal-tables/system-versioned-tables). ## Examples ### ORDER BY and LIMIT How to use the [ORDER BY](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by) and [LIMIT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/limit) clauses: ```sql DELETE FROM page_hit ORDER BY TIMESTAMP LIMIT 1000000; ``` How to use the `RETURNING` clause: ```sql 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. ```sql DELETE post FROM blog INNER JOIN post WHERE blog.id = post.blog_id; ``` ### Deleting from the Same Source and Target ```sql CREATE TABLE t1 (c1 INT, c2 INT); DELETE FROM t1 WHERE c1 IN (SELECT b.c1 FROM t1 b WHERE b.c2=0); ``` {% tabs %} {% tab title="Current" %} The statement returns: ``` Query OK, 0 rows affected (0.00 sec) ``` {% endtab %} {% tab title="< 10.3.1" %} The statement returns: ```sql ERROR 1093 (HY000): Table 't1' is specified twice, both as a target for 'DELETE' AND AS a separate source FOR ``` {% endtab %} {% endtabs %} ### CTE Single-Table ```sql WITH cte1 AS (SELECT * FROM t1 WHERE c < 5), cte2 AS (SELECT * FROM t2 WHERE b < 5) DELETE FROM t3 WHERE t3.a = (SELECT a FROM cte1 WHERE b IN (SELECT b FROM cte2)); ``` ### CTE Multi-Table ```sql WITH cte1 AS (SELECT * FROM t1 WHERE c < 5), cte2 AS (SELECT * FROM t2 WHERE b < 5) DELETE FROM t3 USING t3, cte1 WHERE t3.a = cte1.a AND cte1.b IN (SELECT b FROM cte2); ``` ```sql WITH cte1 AS (SELECT * FROM t1 WHERE c < 5), cte2 AS (SELECT * FROM t2 WHERE b < 5) DELETE FROM t3 USING t3, cte1, cte2 WHERE cte1.a AND cte1.b = ctd2.b; ``` ## See Also * [How IGNORE works](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/ignore) * [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select) * [ORDER BY](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by) * [LIMIT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/limit) * [REPLACE ... RETURNING](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/replacereturning) * [INSERT ... RETURNING](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insertreturning) * [Returning clause](https://www.youtube.com/watch?v=n-LTdEBeAT4) (video) *This page is licensed: GPLv2, originally from* [*fill\_help\_tables.sql*](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql) {% @marketo/form formId="4316" %} [^1]: CTE (Common Table Expression): A temporary, named result set that exists only for the duration of a single `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statement, used to make complex queries more readable. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/delete.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.