Data Addition & Deletion

1. Data Insertion

Syntax:

INSERT INTO <TABLE_NAME> [(COLUMN_NAME[, COLUMN_NAME]*)]? VALUES (COLUMN_VALUE[, COLUMN_VALUE]*)

Detailed syntax reference

Example 1: Specified Columns Insertion

INSERT INTO table1(region, plant_id, device_id, time, temperature, humidity) VALUES ('Hamburg', '1001', '100', '2025-11-26 13:37:00', 90.0, 35.1);

INSERT INTO table1(region, plant_id, device_id, time, temperature) VALUES ('Hamburg', '1001', '100', '2025-11-26 13:38:00', 91.0);

Example 2: NULL Value Insertion

Equivalent to the example above

INSERT INTO table1(region, plant_id, device_id, model_id, maintenance, time, temperature, humidity) VALUES ('Hamburg', '1001', '100', null, null, '2025-11-26 13:37:00', 90.0, 35.1);

INSERT INTO table1(region, plant_id, device_id, model_id, maintenance, time, temperature, humidity) VALUES ('Hamburg', '1001', '100', null, null, '2025-11-26 13:38:00', 91.0, null);

Example 3: Multi-row Insertion

INSERT INTO table1
VALUES 
('2025-11-26 13:37:00', 'Frankfurt', '1001', '100', 'A', '180', 90.0, 35.1, true, '2025-11-26 13:37:34'),
('2025-11-26 13:38:00', 'Frankfurt', '1001', '100', 'A', '180', 90.0, 35.1, true, '2025-11-26 13:38:25');

INSERT INTO table1
(region, plant_id, device_id, model_id, maintenance, time, temperature, humidity, status, arrival_time) 
VALUES 
('Frankfurt', '1001', '100', 'A', '180', '2025-11-26 13:37:00', 90.0, 35.1, true, '2025-11-26 13:37:34'),
('Frankfurt', '1001', '100', 'A', '180', '2025-11-26 13:38:00', 90.0, 35.1, true, '2025-11-26 13:38:25');

Example 4: Query Write-back

insert into target_table select time,region,device_id,temperature from table1 where region = 'bj';

insert into target_table(time,device_id,temperature) table table3;

insert into target_table (select t1.time, t1.region as region, t1.device_id as device_id, t1.temperature as temperature from table1 t1 where t1.time in (select t2.time from table2 t2 where t2.region = 'shh'));

2. Data Update

Syntax:

UPDATE <TABLE_NAME> SET updateAssignment (',' updateAssignment)* (WHERE where=booleanExpression)?

updateAssignment
    : identifier EQ expression
    ;

Detailed syntax reference

Example:

update table1 set b = a where substring(a, 1, 1) like '%';

3. Data Deletion

Syntax:

DELETE FROM <TABLE_NAME> [WHERE_CLAUSE]?

WHERE_CLAUSE:
    WHERE DELETE_CONDITION

DELETE_CONDITION:
    SINGLE_CONDITION
    | DELETE_CONDITION AND DELETE_CONDITION
    | DELETE_CONDITION OR DELETE_CONDITION

SINGLE_CONDITION:
    TIME_CONDITION | ID_CONDITION | ATTRIBUTE_CONDITION

TIME_CONDITION:
    time TIME_OPERATOR LONG_LITERAL

TIME_OPERATOR:
    < | > | <= | >= | =

ID_CONDITION:
    identifier = STRING_LITERAL

ATTRIBUTE_CONDITION:
    attribute_identifier (= | != | > | >= | < | <= | LIKE) STRING_LITERAL
    | attribute_identifier IS [NOT] NULL
    | attribute_identifier IN (STRING_LITERAL [, STRING_LITERAL] ...)

• Conditions can filter by time, tags, and attributes, and can only be combined using AND and OR.
• Time conditions support <, >, =, <=, and >=; tag conditions support only =.
• Since V2.0.12.1, attribute conditions support =, !=, IS NULL, IS NOT NULL, >, >=, <, <=, LIKE, and IN. The left operand must be an attribute column name, without an expression; the right operand must be a string literal.
• Each conjunction (a group of conditions joined by AND) that contains an attribute filter can match at most 1,000 devices. If it matches more, the entire DELETE statement is rejected. Add attribute filters to narrow the range, or remove all attribute filters and confirm the deletion range again.

Example 1: Full Table Deletion

DELETE FROM table1;

Example 2: Time-range Deletion

Single time range

DELETE FROM table1 WHERE time <= 2024-11-29 00:00:00;

Multiple time ranges

DELETE FROM table1  WHERE time >= 2024-11-27 00:00:00  and time <= 2024-11-29 00:00:00;

Example 3: Device-Specific Deletion

Delete data for specific device

DELETE FROM table1 
WHERE device_id='101' AND model_id = 'B';

Delete data for device within time range

DELETE FROM table1 
WHERE time >= '2024-11-27 16:39:00' AND time <= '2024-11-29 16:42:00'
    AND device_id='101' AND model_id = 'B';

Delete data for specific device model

DELETE FROM table1 WHERE model_id = 'B';

Since V2.0.12.1, an attribute column can also be combined with a time condition:

DELETE FROM table1
WHERE model_id = 'B' AND time >= 2024-11-27 16:39:00;

4. Device Deletion

Syntax:

DELETE DEVICES FROM tableName=qualifiedName (WHERE booleanExpression)?

Example: Delete specified device and all associated data

DELETE DEVICES FROM table1 WHERE device_id = '101';