Delete data from tables - 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.
The DELETE syntax is supported by LindormTSDB 3.4.19 and later versions. For more information about how to view or upgrade the version of LindormTSDB, see Release notes of LindormTSDB and Upgrade the minor engine version of a Lindorm instance.
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 you use the DELETE syntax to delete data, take note of the following items:

For LindormTable:

LindormTable earlier than 2.7.5 allows you to execute the DELETE statement to delete only a single row of data. You must specify all primary keys of a specific row in the statement. Otherwise, an error is reported. LindormTable 2.7.5 and later allows you to execute the DELETE statement to delete multiple rows of data at the same time. By default, the batch deletion feature is disabled. To use this feature, contact Lindorm technical support. For more information about how to view or upgrade the version of LindormTable, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.
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.
When you execute an UPSERT statement to insert a row of data, you can use hints in the statement to specify a timestamp in the future as the version of the row that you want to insert. In this case, when you delete the row of data by executing a DELETE statement, the data is not deleted even if the statement is successful. For more information about data versioning, see Use hints to implement data versioning.

For LindormTSDB:

You can specify only filter conditions for the TAG column in the WHERE clause of a DELETE statement.
The DELETE operation can delete only all data in the time series that meet the TAG filter conditions. The DELETE operation cannot delete a single data record.

Conditions (where_clause)

Both LindormTable and LindormTSDB support the WHERE clause (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: The WHERE clause supports a wide range of filter conditions, which is similar to the WHERE clause of the SELECT statement.
Important
An error, such as a timeout, that occurs during a DELETE operation may cause partial data deletion.

LindormTSDB

In LindormTSDB, the where_claim field in the WHERE clause must be the TAG column. You can use the DELETE syntax to delete all data from the time series that meet the TAG filter conditions.

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

Create a table and insert data into the table. Sample code:

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

You can execute the SELECT * FROM sensor; statement to check whether the data is deleted. Expected output:

+------+------+------+------+
| 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

You can execute the SELECT * FROM sensor; statement to check whether the data is deleted.