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 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.

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;

Was this Article Helpful?

Thanks For Your Feedback!