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. Refer to the SELECT statement documentation for full definition of table_references.


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.


  • The DELETE statement deletes rows from tbl_name and returns the number of deleted rows.

  • Although DELETE supports referencing multiple tables using either joins or subqueries, SingleStore only supports deleting from one table in a DELETE statement.

  • If the maximum_table_memory limit has been reached, DELETE queries can still be executed to remove data from the table, but large DELETE queries may fail if the maximum_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 of DELETE; however, if you do need to run DELETE over a large number of rows in a rowstore table, perform DELETEs in smaller batches using LIMIT to minimize the additional memory usage.

  • This command must be run on the master aggregator or a child aggregator node (see Node Requirements for SingleStore 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;
WHERE id IN (SELECT id FROM myother) LIMIT 10;
DELETE t_rec FROM t_rec JOIN t_invalid
DELETE t_rec FROM t_rec JOIN
(SELECT id FROM t_rec ORDER BY score LIMIT 10)temp
DELETE b FROM a, b, c
DELETE x FROM looooooooooongName as x, y

Last modified: January 9, 2023

Was this article helpful?