Remove Rows & Timelines via DELETE in Lindorm - Lindorm

Use the DELETE statement to delete rows from a wide table (LindormTable) or to delete entire time series that match TAG filter conditions from a time series table (LindormTSDB).

Applicable engines and versions

Engine	Version requirement
LindormTable	All versions
LindormTSDB	3.4.19 and later for timeline deletion

To check or upgrade your current version, see the version guide and minor version update instructions:

LindormTable: LindormTable version guide | Upgrade the minor engine version
LindormTSDB: Version Guide | Upgrade the minor engine version

Syntax

delete_statement ::=  DELETE FROM table_identifier
                      WHERE where_clause
where_clause     ::=  relation ( AND|OR relation )*
relation         ::=  column_name operator term
                      | '(' column_name ( ',' column_name )* ')' operator tuple_literal
operator         ::=  '=' | '<' | '>' | '<=' | '>=' | '!=' | IN | IS NOT?  NULL | LIKE

Specify the rows to delete using the WHERE clause.

Usage notes

LindormTable

How DELETE works

After a DELETE statement runs, Lindorm writes a delete marker to the target data instead of reading the data first. The operation reports success regardless of whether the target row actually exists.

Important

If you write data using UPSERT with a future timestamp as the data version, a subsequent DELETE on that data will not physically remove it even if the statement succeeds. See Multi-version Data Management for details.

Version-based behavior

Version	Batch deletion	WHERE clause requirement
Earlier than 2.7.5	Not available	All primary key columns with `=` conditions (single-row only)
2.7.5 and later, batch deletion disabled	Disabled (default)	All primary key columns with `=` conditions (single-row only)
2.7.5 and later, batch deletion enabled	Enabled	Full range of filter conditions, similar to `SELECT`

Versions earlier than 2.8.2.26 support only single-row deletion. Starting from 2.8.2.26, batch deletion is available but disabled by default. To enable it, contact Lindorm technical support.

Important

An error during a batch DELETE operation—such as a timeout—may cause partial data deletion.

LindormTSDB

DELETE on a time series table removes all data in the matching time series, not individual data points.

WHERE clause constraints for LindormTSDB:

Filter conditions must target TAG columns (VARCHAR type) only.
The column_identifier field in the relation expression must be a VARCHAR (TAG) column.
DELETE cannot target individual data records—it always removes the full time series that satisfies the TAG filter condition.

Examples

The following examples use this table:

-- Create a table named sensor.
CREATE TABLE sensor (
  p1 INTEGER NOT NULL,
  p2 INTEGER NOT NULL,
  c1 VARCHAR,
  c2 VARCHAR,
  PRIMARY KEY(p1, p2)
);

-- Insert data into the table.
UPSERT INTO sensor(p1, p2, c1, c2) VALUES (1,1,'a','a'),(2,2,'b','b'),(3,3,'c','c');

Delete a single row

Specify all primary key columns in the WHERE clause to delete a single row.

DELETE FROM sensor WHERE p1 = 1 AND p2 = 1;

Run SELECT * FROM sensor; to verify the result:

+------+------+------+------+
| p1   | p2   | c1   | c2   |
+------+------+------+------+
|    2 |    2 | b    | b    |
|    3 |    3 | c    | c    |
+------+------+------+------+

Delete multiple rows (batch deletion)

Important

Batch deletion is in public preview. To enable it, contact Lindorm technical support (DingTalk ID: s0s3eg3).

With batch deletion enabled, use a broader WHERE clause to delete multiple rows at once.

DELETE FROM sensor WHERE c1 > 'b' OR p2 > 1;

Run SELECT * FROM sensor; to verify that the expected rows are deleted.