SingleStore DB


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 DB 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, perform them 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 DB 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.

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
  WHERE =;

DELETE t_rec FROM t_rec JOIN
  (SELECT id FROM t_rec ORDER BY score LIMIT 10)temp

DELETE b FROM a, b, c
  WHERE = OR =;

DELETE x FROM looooooooooongName as x, y
  WHERE =;