DELETE

Deletes rows from the specified table.

Syntax

DELETE FROM <table> [[AS] <alias>] [USING <from_item>] WHERE <condition>

Parameters

Parameter	Description
`<table>`	The table to delete rows from.
`<from_item>`	A table expression allowing columns from other tables to appear in the `WHERE` condition. This uses the same syntax as the `FROM` clause of a `SELECT` statement; for example, an alias for the table name can be specified. Do not repeat the target table as a `from_item` unless you wish to set up a self-join (in which case it must appear with an alias in the `from_item`).
`<condition>`	A Boolean expression. Only rows for which this expression returns `true` will be deleted. Condition can have subqueries doing semi-join with other table(s).

The DELETE FROM <table> without <expression> will delete all rows from the table. It is equivalent to a TRUNCATE TABLE statement.

Remarks

Deleted rows are marked for deletion, but are not automatically cleaned up. You can monitor fragmentation in information_schema.tables to understand how many rows are marked for deletion out of total rows; fragmentation = (rows marked for deletion / total rows). Total row count in information_schema.tables excludes the number of rows marked for deletion. Query performance is not materially impacted by delete marks.

To mitigate fragmentation, use the VACUUM command to manually clean up deleted rows.

Example

The following example deletes entries from the products table where the quantity is less than 10:

DELETE FROM products WHERE quantity < 10

Table before:

product	quantity
wand	9
broomstick	21
robe	1
quidditch gloves	10
cauldron	16
quill	100

Table after:

product	quantity
broomstick	21
quidditch gloves	10
cauldron	16
quill	100

Example specifying other tables in the USING clause

This example deletes all the products from stores that went out of business.

DELETE FROM products USING suppliers WHERE products.product = suppliers.product AND suppliers.store = 'Ollivanders'

Table products before:

product	quantity
wand	9
broomstick	21
robe	1
quidditch gloves	10
cauldron	16
quill	100

Table suppliers before:

product	store
wand	Ollivanders
broomstick	Quality Quidditch Supplies
robe	Madam Malkin’s
quidditch gloves	Quality Quidditch Supplies
cauldron	Apothecary
quill	Amanuensis Quills

Table products after:

product	quantity
broomstick	21
robe	1
quidditch gloves	10
cauldron	16
quill	100

Known limitations

Below are some known limitations of the DELETE command.

DELETE cannot be used on tables that have certain aggregating indexes. An attempt to issue a DELETE statement on a table with an aggregating index outside of the below defined will fail- these table level aggregating indexes need to be dropped first. DELETE can be used on tables that have aggregating indexes containing the following aggregating functions:
Queries against tables with deleted rows are supported and can be run. However, expect slower performance.