You are viewing an older version of this section. View current production version.
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 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.
DELETE statement deletes rows from
tbl_name and returns the number of deleted rows.
DELETE supports referencing multiple tables using either joins or subqueries, SingleStore DB only supports deleting from one table in a
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 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; 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;