Delete data from a table - Lindorm - Alibaba Cloud Documentation Center

You can use the DELETE syntax to delete data from a wide table or delete time series that meet the specified conditions from a time series table.

Applicable engines and versions

The DELETE syntax is applicable to LindormTable and LindormTSDB.
LindormTSDB version 3.4.19 and later support timeline deletion. To view or upgrade your current version, see Version Guide and minor version update.
The DELETE syntax is supported by all versions of LindormTable.

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

Usage notes

Important

Before using the DELETE statement to delete data, note the following.

For LindormTable:

For LindormTable versions earlier than 2.8.2.26, the DELETE statement supports only single-row deletion. This means that the filter condition must specify all primary key columns to delete a row. Otherwise, an error occurs. LindormTable 2.8.2.26 and later support batch deletion. This feature is disabled by default. To use this feature, contact Lindorm technical support. To view or upgrade the current version, see LindormTable version guide and minor version update.
After you execute a DELETE statement, Lindorm adds a delete marker to the data that you want to delete, instead of querying the data before the delete operation. Therefore, the delete operation is successful regardless of whether the row that you want to delete exists.
If you use an UPSERT statement to write data and use hints to specify a future timestamp as the data version, executing a DELETE operation does not actually delete the data—even if the DELETE statement succeeds. For more information, see Multi-version Data Management.

For the time-series engine:

In the WHERE clause of a DELETE statement, you can specify filter conditions only for TAG columns.
The DELETE operation cannot delete a specific data record. It deletes all data in the time series that meet the TAG filter conditions.

Delete condition (where_clause)

Both LindormTable and LindormTSDB support specifying deletion conditions (where_clause).

LindormTable

LindormTable earlier than 2.7.5: The WHERE clause must contain the equivalent filter condition (column_name=value) of all primary key columns and you can delete only a single row of data.
LindormTable 2.7.5 and later, with batch deletion disabled: The WHERE clause must contain the equivalent filter condition (column_name=value) of all primary key columns and you can delete only a single row of data.
LindormTable 2.7.5 and later with batch deletion enabled support a wide range of filter conditions in the WHERE clause, similar to those supported by the WHERE clause of a SELECT statement.
Important
An error, such as a timeout, that occurs during a DELETE operation may cause partial data deletion.

LindormTSDB

The where_clause must specify a TAG column. The delete operation removes all data from the time series that satisfy the TAG filter condition.

Relational expression (relation)

When you use the DELETE syntax to delete a time series from a time series table, the column_identifier field in the relation parameter must be the VARCHAR (TAG) column.

Example

Assume the table schema and its data are as follows:

-- 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 to 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 of data

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

Verify the result

Execute the SELECT * FROM sensor; statement to verify that the data is deleted. The expected output is as follows:

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

Delete multiple rows of data at the same time

Important

This feature is in public preview. To use this feature, contact Lindorm technical support (DingTalk ID: s0s3eg3).

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

Verify the result

Execute the SELECT * FROM sensor; statement to verify that the data is deleted.