On this page
DELETE is a DML statement that deletes rows in a table.
DELETE FROM tbl_name[WHERE expr][LIMIT row_count]DELETE tbl_name FROM table_references[WHERE expr][LIMIT row_count]
One or more tables to reference during the delete operation.
Table from which rows will be deleted.
One or more expression that evaluates to true for each row to be deleted.
The maximum number of rows that can be deleted.
DELETEstatement deletes rows from
tbl_and returns the number of deleted rows.
DELETEsupports referencing multiple tables using either joins or subqueries, SingleStoreDB only supports deleting from one table in a
maximum_limit has been reached,
DELETEqueries can still be executed to remove data from the table, but large
DELETEqueries may fail if the
maximum_limit has been reached.
Caution should be taken as
DELETEqueries 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
DELETEcommand 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,
DELETEqueries 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
TRUNCATEdoes not incur the memory penalty of
DELETE; however, if you do need to run
DELETEover a large number of rows in a rowstore table, perform
DELETEs in smaller batches using
LIMITto 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).
Note that when running this command on reference tables you must connect to the master aggregator.
Writing to multiple databases in a transaction is not supported.
Refer to the Permission Matrix for the required permission.
DELETE FROM mytbl WHERE seq = 1;DELETE FROM mytable LIMIT 100000;DELETE FROM mytblWHERE id IN (SELECT id FROM myother) LIMIT 10;DELETE t_rec FROM t_rec JOIN t_invalidWHERE t_rec.id = t_invalid.id;DELETE t_rec FROM t_rec JOIN(SELECT id FROM t_rec ORDER BY score LIMIT 10)tempWHERE t_rec.id=temp.id;DELETE b FROM a, b, cWHERE a.name = b.name OR b.name = c.name;DELETE x FROM looooooooooongName as x, yWHERE x.id = y.id;
Last modified: January 9, 2023