Skip to main content
VeloDB Cloud 26.x is based on Apache Doris 4.x. See Version Mapping.

Delete Rows with DELETE

VeloDB Cloud executes the DELETE statement through the MySQL protocol to remove data from a specified table or partition based on conditions. Two usage forms are supported:

  • Predicate-based delete: Delete data matching simple WHERE predicate combinations.
  • Multi-table joined delete (USING): On a primary-key table (Unique Key), use the USING clause to join other tables for deletion.

Choose a delete operation​

ScenarioRecommended approachApplicable table model
Delete by column value conditionPredicate-based deleteDuplicate / Aggregate / Unique
Bulk delete by partitionPredicate-based delete + PARTITIONAll models
Precise delete via multi-table joinDelete with USING clauseUnique Key only
View delete historySHOW DELETEPredicate-delete path only

Delete rows with filter predicates​

Write the statement​

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

Set statement parameters​

Set required parameters

ParameterDescription
table_nameThe target table from which to delete data
column_nameA column belonging to table_name
opLogical comparison operator. Supported: =, >, <, >=, <=, !=, in, not in
value | value_listA single value or value list used for the logical comparison

Set optional parameters

ParameterDescription
PARTITION / PARTITIONSSpecifies the partition name on which to perform the delete. If the table does not have the specified partition, an error is reported
table_aliasAlias of the table

Check delete constraints​

  • Aggregate Key tables: Conditions can only be specified on Key columns. If a selected Key column does not exist in a Rollup, the delete cannot be executed.
  • Non-Unique partitioned tables: Specify the partitions or use a predicate from which the query engine can infer them. In particular, partition inference may be unavailable when:
    1. The conditions do not include the partition column.
    2. The op for the partition column is not in.
  • Non-Unique partitioned tables: When no partition is specified and the partition cannot be inferred, the session variable delete_without_partition = true must be set. In this case, the delete operation applies to all partitions.

Run predicate deletes​

The examples below use my_table, a Unique-Key list-partitioned table on k1 with partitions p1 and p2:

CREATE TABLE my_table (
k1 INT,
status VARCHAR(32),
dt DATE
)
UNIQUE KEY (k1)
PARTITION BY LIST (k1) (
PARTITION p1 VALUES IN (1, 2, 3, 4, 5),
PARTITION p2 VALUES IN (6, 7, 8, 9, 10)
)
DISTRIBUTED BY HASH (k1) BUCKETS 1;

INSERT INTO my_table VALUES
(1, 'active', '2024-09-01'),
(3, 'outdated', '2024-10-15'),
(5, 'active', '2024-10-20'),
(6, 'outdated', '2024-10-05'),
(8, 'outdated', '2024-10-25'),
(10, 'active', '2024-11-10');

Delete data in a specified partition where a column equals a fixed value​

Delete data in a specified partition where a column equals a fixed value
DELETE FROM my_table PARTITION p1
WHERE k1 = 3;

Delete data in a specified partition that satisfies a compound condition​

Delete data in a specified partition that satisfies a compound condition
DELETE FROM my_table PARTITION p1
WHERE k1 >= 3 AND status = "outdated";

Delete data in multiple partitions filtered by a time range​

Delete data in multiple partitions filtered by a time range
DELETE FROM my_table PARTITIONS (p1, p2)
WHERE k1 >= 3 AND dt >= "2024-10-01" AND dt <= "2024-10-31";

Delete rows with USING​

When multiple tables must be joined to precisely identify the data to be deleted, use the USING clause.

Write the statement​

DELETE FROM table_name [table_alias]
[PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
[USING additional_tables]
WHERE condition;

Set statement parameters​

Set required parameters

ParameterDescription
table_nameThe target table from which to delete data
WHERE conditionThe condition used to select rows for deletion

Set optional parameters

ParameterDescription
PARTITION / PARTITIONSSpecifies the partition name on which to perform the delete. If the table does not have the specified partition, an error is reported
table_aliasAlias of the table
USING additional_tablesOther tables used for joining

Check delete constraints​

  • Only supported on Unique Key model tables.

Run a joined delete​

Run a joined delete

The following example shows how to delete data from t1 based on the join result of t2 and t3.

Create the tables

CREATE TABLE t1
(id INT, c1 BIGINT, c2 STRING, c3 DOUBLE, c4 DATE)
UNIQUE KEY (id)
DISTRIBUTED BY HASH (id)
PROPERTIES("function_column.sequence_col" = "c4");

CREATE TABLE t2
(id INT, c1 BIGINT, c2 STRING, c3 DOUBLE, c4 DATE)
DISTRIBUTED BY HASH (id);

CREATE TABLE t3
(id INT)
DISTRIBUTED BY HASH (id);

Insert test data

INSERT INTO t1 VALUES
(1, 1, '1', 1.0, '2000-01-01'),
(2, 2, '2', 2.0, '2000-01-02'),
(3, 3, '3', 3.0, '2000-01-03');

INSERT INTO t2 VALUES
(1, 10, '10', 10.0, '2000-01-10'),
(2, 20, '20', 20.0, '2000-01-20'),
(3, 30, '30', 30.0, '2000-01-30'),
(4, 4, '4', 4.0, '2000-01-04'),
(5, 5, '5', 5.0, '2000-01-05');

INSERT INTO t3 VALUES
(1),
(4),
(5);

Execute the joined delete

DELETE FROM t1
USING t2 INNER JOIN t3 ON t2.id = t3.id
WHERE t1.id = t2.id;

Verify the result: The row with id = 1 in t1 is deleted.

SELECT * FROM t1 ORDER BY id;
+----+----+----+--------+------------+
| id | c1 | c2 | c3 | c4 |
+----+----+----+--------+------------+
| 2 | 2 | 2 | 2.0 | 2000-01-02 |
| 3 | 3 | 3 | 3.0 | 2000-01-03 |
+----+----+----+--------+------------+

Adjust delete execution​

ConfigurationScopeDescriptionDefault
insert_timeoutSessionA delete is treated as a special load and is bounded by this value. Adjust it with SET insert_timeout = xxx, in seconds-
max_allowed_in_element_num_of_deleteService-managedLimits the number of elements in an IN predicate on the predicate-delete path. Use smaller batches or contact support if the limit is reachedCompiled default: 1024; effective warehouse settings can differ

View delete history​

Use SHOW DELETE to view records produced by the predicate-delete path. Unique Key deletes that run through the INSERT-based path, including joined deletes, are not a complete part of this history. Check the statement result and query the target keys to verify those deletes.

Write the statement

SHOW DELETE [FROM db_name];

Run the example

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

Plan deletion workloads​

Choose a method based on the data to remove and the table model:

Table modelExecution considerationsRecommended action
Duplicate Key / Aggregate KeyRepeated predicate deletes add version and cleanup work. Aggregate Key predicates are restricted to key columnsBatch related conditions and use whole-partition cleanup when appropriate
Unique KeyThe INSERT-based delete path reads matching rows and writes delete markers. A large matching set can consume substantial computeBatch primary-key events through loading for CDC, or use a joined delete for keys already stored in another table

Deletion and replacement jobs consume warehouse compute. Schedule large jobs around latency-sensitive queries and verify results before deleting staging data.

Resolve common questions​

Specify or infer the target partitions

For non-Unique partitioned tables, when the conditions do not include the partition column or use not in, VeloDB Cloud cannot infer the partition. You can:

  • Explicitly specify PARTITION in the DELETE statement.
  • Or set the session variable SET delete_without_partition = true so that the delete applies to all partitions.

Restrict aggregate-table predicates to key columns

No. Aggregate tables only allow delete conditions on Key columns.

Choose a Unique Key target for USING

Only Unique Key model tables are supported as the delete target.

Reduce the IN predicate size

Split the predicate into smaller batches. For a Unique Key table, consider loading the keys or joining a staging table instead. The limit is service-managed in VeloDB Cloud. Contact VeloDB Cloud support if you need a configuration review.

Adjust a timed-out delete

Use SET insert_timeout = xxx (in seconds) to increase the timeout.