Skip to main content

SingleStoreDB v8.1

DELETE

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, SingleStoreDB 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 SingleStoreDB Commands).

  • Writing to multiple databases in a transaction is not supported.

  • See the Permission Matrix for the required permission.

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;
In this section: