Delete data by using the DELETE statement

Syntax

DELETE FROM table_name [PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
WHERE
column_name op { value | value_list } [ AND column_name op { value | value_list } ...];

Parameters

Parameter	Description
`table_name`	The table from which to delete data.
PARTITION partition_name \| PARTITIONS (partition_name[, partition_name])	Optional. Specifies one or more partitions. Returns an error if a partition does not exist.
`column_name`	The column used in the filter condition.
`op`	Logical operator. Valid values: `=`, `>`, `<`, `>=`, `<=`, `!=`, `IN`, `NOT IN`.
value \| value_list	The value or list of values for comparison.

Constraints

Key columns only for Aggregate Key and Unique Key models: Conditions must reference Key columns only.
Rollup compatibility: DELETE fails if the specified Key columns do not exist in a Rollup or materialized view.
AND operator required: Multiple conditions must use AND. For OR logic, run separate DELETE statements.
No manual label: DELETE does not support manually specifying a label.
Temporary performance impact: Query performance may decrease temporarily after DELETE. More conditions increase the impact.

Examples

Delete data from partition p1 where k1 equals 1:

DELETE FROM test_tbl PARTITION p1 WHERE k1 = 1;

Delete data from partition p1 where k1 is greater than 80:

DELETE FROM test_tbl PARTITION p1 WHERE k1 > 80;

Returned results

DELETE returns one of three results:

Transaction executed successfully (VISIBLE)

Delete completed. Results are immediately visible.

Query OK, 0 rows affected (0.04 sec)
{'label':'delete_e7830c72-eb14-4cb9-bbb6-eebd4511d251', 'status':'VISIBLE', 'txnId':'4005'}

Transaction committed but not yet visible (COMMITTED)

Transaction committed but the version is not yet released. Results become visible after release.

Query OK, 0 rows affected (0.04 sec)
{'label':'delete_e7830c72-eb14-4cb9-bbb6-eebd4511d251', 'status':'COMMITTED', 'txnId':'4005', 'err':'delete job is committed but may be taking effect later'}

Run the SHOW DELETE statement to check the status later.

Transaction failed (ERROR)

Transaction failed and was aborted.

ERROR 1064 (HY000): errCode = 2, detailMessage = {Cause of the error}

On timeout, the error includes the duration and unfinished replicas:

ERROR 1064 (HY000): errCode = 2, detailMessage = failed to delete replicas from job: 4005, Unfinished replicas:10000=60000, 10001=60000, 10002=60000

Quick reference for result status

Result	Meaning
`ERROR 1064 (HY000)`	The DELETE operation failed.
`Query OK` with `status: VISIBLE`	Data is deleted and visible.
`Query OK` with `status: COMMITTED`	Data is committed but not yet visible. Wait and then run `SHOW DELETE` to verify.

Result parameters

Parameter	Description
`rows affected`	Always `0`. SelectDB uses logical deletion.
`label`	Auto-generated job identifier, unique within the database.
`status`	`VISIBLE`: results are visible. `COMMITTED`: results are not yet visible.
`txnId`	The transaction ID of the delete operation.
`err`	Error information, if any.

View deletion history

Run SHOW DELETE to view past delete operations:

SHOW DELETE [FROM db_name];

Example:

SHOW DELETE FROM test_db;

+-----------+---------------+---------------------+-----------------+----------+
| TableName | PartitionName | CreateTime          | DeleteCondition | State    |
+-----------+---------------+---------------------+-----------------+----------+
| empty_tbl | p3            | 2020-04-15 23:09:35 | k1 EQ "1"       | FINISHED |
| test_tbl  | p4            | 2020-04-15 23:09:53 | k1 GT "80"      | FINISHED |
+-----------+---------------+---------------------+-----------------+----------+
2 rows in set (0.00 sec)

Partition handling

For partitioned tables, specify one or more partitions. If none is specified, SelectDB infers partitions from the WHERE conditions.

Inference fails when:

The conditions do not reference a partition key column.
The partition key uses the NOT IN operator.

If inference fails, set delete_without_partition to true to apply DELETE to all partitions:

-- Set for the current session
SET delete_without_partition = true;

-- Set globally
SET GLOBAL delete_without_partition = true;

This applies DELETE to all partitions.

Configuration

Timeout

Delete operations time out between 30 seconds and 5 minutes. Formula:

TIMEOUT = MIN(load_straggler_wait_second, MAX(30, tablet_delete_timeout_second * tablet_num))

Timeout parameters:

Parameter	Type	Default	Description
`tablet_delete_timeout_second`	FE configuration	2 seconds	Per-tablet timeout contribution. Example: 5 tablets produce 10 seconds, clamped to the 30-second minimum.
`load_straggler_wait_second`	FE configuration	300 seconds	Upper bound for delete timeout. Increase for large deletes that need more than 5 minutes.
`query_timeout`	Session variable	900 seconds	SQL-level timeout. Increase with `SET query_timeout = xxx`.

IN predicate limit

Parameter	Type	Default	Description
`max_allowed_in_element_num_of_delete`	FE configuration	1024	Maximum elements allowed in an IN predicate for DELETE.

How it works

Each DELETE runs as a separate import job. SelectDB uses two-phase commit: the transaction is committed, then a version is released. Results become visible only after release.

Data is deleted from both the base table and its Rollup tables.