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
WHEREpredicate combinations. - Multi-table joined delete (USING): On a primary-key table (Unique Key), use the
USINGclause to join other tables for deletion.
Choose a delete operation
| Scenario | Recommended approach | Applicable table model |
|---|---|---|
| Delete by column value condition | Predicate-based delete | Duplicate / Aggregate / Unique |
| Bulk delete by partition | Predicate-based delete + PARTITION | All models |
| Precise delete via multi-table join | Delete with USING clause | Unique Key only |
| View delete history | SHOW DELETE | Predicate-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
| Parameter | Description |
|---|---|
table_name | The target table from which to delete data |
column_name | A column belonging to table_name |
op | Logical comparison operator. Supported: =, >, <, >=, <=, !=, in, not in |
value | value_list | A single value or value list used for the logical comparison |
Set optional parameters
| Parameter | Description |
|---|---|
PARTITION / PARTITIONS | Specifies the partition name on which to perform the delete. If the table does not have the specified partition, an error is reported |
table_alias | Alias 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:
- The conditions do not include the partition column.
- The
opfor the partition column isnot in.
- Non-Unique partitioned tables: When no partition is specified and the partition cannot be inferred, the session variable
delete_without_partition = truemust 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
| Parameter | Description |
|---|---|
table_name | The target table from which to delete data |
WHERE condition | The condition used to select rows for deletion |
Set optional parameters
| Parameter | Description |
|---|---|
PARTITION / PARTITIONS | Specifies the partition name on which to perform the delete. If the table does not have the specified partition, an error is reported |
table_alias | Alias of the table |
USING additional_tables | Other 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
| Configuration | Scope | Description | Default |
|---|---|---|---|
insert_timeout | Session | A 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_delete | Service-managed | Limits the number of elements in an IN predicate on the predicate-delete path. Use smaller batches or contact support if the limit is reached | Compiled 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 model | Execution considerations | Recommended action |
|---|---|---|
| Duplicate Key / Aggregate Key | Repeated predicate deletes add version and cleanup work. Aggregate Key predicates are restricted to key columns | Batch related conditions and use whole-partition cleanup when appropriate |
| Unique Key | The INSERT-based delete path reads matching rows and writes delete markers. A large matching set can consume substantial compute | Batch 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
PARTITIONin theDELETEstatement. - Or set the session variable
SET delete_without_partition = trueso 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.