# 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)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](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.