Reference and syntax for the DELETE command.
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). |
<settings> | List of query-specific settings overrides. |
DELETE FROM <table>
without <expression>
will delete all rows from the table. It is equivalent to a TRUNCATE TABLE statement.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.
products
table where the quantity
is less than 10
:
product | quantity |
---|---|
wand | 9 |
broomstick | 21 |
robe | 1 |
quidditch gloves | 10 |
cauldron | 16 |
quill | 100 |
product | quantity |
---|---|
broomstick | 21 |
quidditch gloves | 10 |
cauldron | 16 |
quill | 100 |
products
before:
product | quantity |
---|---|
wand | 9 |
broomstick | 21 |
robe | 1 |
quidditch gloves | 10 |
cauldron | 16 |
quill | 100 |
suppliers
before:
product | store |
---|---|
wand | Ollivanders |
broomstick | Quality Quidditch Supplies |
robe | Madam Malkin’s |
quidditch gloves | Quality Quidditch Supplies |
cauldron | Apothecary |
quill | Amanuensis Quills |
products
after:
product | quantity |
---|---|
broomstick | 21 |
robe | 1 |
quidditch gloves | 10 |
cauldron | 16 |
quill | 100 |