DELETE
DELETE
is a DML statement that deletes rows in a table.
Syntax
DELETE FROM tbl_name [WHERE expr] [LIMIT row_count] DELETE tbl_name FROM table_references [WHERE expr] [LIMIT row_count]
Arguments
table_references
One or more tables to reference during the delete operation. Refer to the SELECT statement documentation for full definition of table_references
.
tbl_name
Table from which rows will be deleted.
where_condition
One or more expression that evaluates to true for each row to be deleted.
row_count
The maximum number of rows that can be deleted.
Remarks
The
DELETE
statement deletes rows fromtbl_name
and returns the number of deleted rows.Although
DELETE
supports referencing multiple tables using either joins or subqueries, SingleStoreDB only supports deleting from one table in aDELETE
statement.If the
maximum_table_memory
limit has been reached,DELETE
queries can still be executed to remove data from the table, but largeDELETE
queries may fail if themaximum_memory
limit has been reached.Caution should be taken as
DELETE
queries allocate extra memory to mark rows as deleted. For rowstore tables, this equates to roughly 40 + 8*number_of_indexes bytes per deleted row. For columnstore tables, the memory usage will be lower because of how rows are marked to be deleted (roughly num_rows_in_table/8 bytes if you delete a row in every segment file in the table).Table memory can be freed when the
DELETE
command is run. For information on when/how much table memory is freed when this command is run, see Memory Management.If the table is narrow, such as containing a small number of int columns,
DELETE
queries will show up as a relatively large spike in memory usage compared to the size of the table.The memory for a deleted row is reclaimed after the transaction commits and the memory is freed asynchronously by the garbage collector.
If you need to delete all records from a large table, use
TRUNCATE
instead.TRUNCATE
does not incur the memory penalty ofDELETE
; however, if you do need to runDELETE
over a large number of rows in a rowstore table, performDELETE
s in smaller batches usingLIMIT
to minimize the additional memory usage.This command must be run on the master aggregator or a child aggregator node (see Node Requirements for SingleStoreDB Commands).
Writing to multiple databases in a transaction is not supported.
See the Permission Matrix for the required permission.
Example
DELETE FROM mytbl WHERE seq = 1; DELETE FROM mytable LIMIT 100000; DELETE FROM mytbl WHERE id IN (SELECT id FROM myother) LIMIT 10; DELETE t_rec FROM t_rec JOIN t_invalid WHERE t_rec.id = t_invalid.id; DELETE t_rec FROM t_rec JOIN (SELECT id FROM t_rec ORDER BY score LIMIT 10)temp WHERE t_rec.id=temp.id; DELETE b FROM a, b, c WHERE a.name = b.name OR b.name = c.name; DELETE x FROM looooooooooongName as x, y WHERE x.id = y.id;