DELETE

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

• For performance limitations refer to UPDATE

• Refer to 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;