Important

Helios features are now enabled during weekly update windows and are no longer directly tied to SingleStore engine releases. Refer to the release notes to view the latest features available in your Helios cluster.

DELETE

The DELETE command 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]

For information on using the RETURNING clause refer DELETE ... RETURNING

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

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

  • Transactions that span more than one database are not supported. An attempt to write to multiple databases within a single transaction fails with the following error: Feature 'Cross-database transactions is not supported by SingleStore.

  • For performance limitations refer to UPDATE

  • Refer to the Permissions Matrix for the required permissions.

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;

Last modified:

Was this article helpful?