AnalyticDB:DELETE

Limitations

• The target table must have a primary key.

• Table aliases are not supported in DELETE statements.

• Each DELETE statement can only delete from one table, even when querying across multiple joined tables.

• To delete all rows from a table or partition, use TRUNCATE TABLE or TRUNCATE TABLE PARTITION instead. For more information, see TRUNCATE TABLE.

Syntax

DELETE FROM table_name
[ WHERE condition ]

DELETE table_name1
FROM table_name1 [INNER JOIN | LEFT JOIN | RIGHT JOIN] table_name2 ON table_name1.column1 = table_name2.column1
[WHERE where_condition]

Examples

Delete rows matching a column value

Delete all rows where customer_name is Alex:

DELETE FROM customer WHERE customer_name='Alex';

Delete rows matching a condition

Delete all rows where age is less than 18:

DELETE FROM customer WHERE age<18;

Delete rows using a JOIN

Delete rows from the customer table where the customer has a matching order and age equals 18:

DELETE customer FROM customer JOIN orders ON customer.id = orders.id WHERE customer.age = 18;

How DELETE works

DELETE operations in AnalyticDB for MySQL are asynchronous. After a DELETE statement runs, the data is marked for deletion but is not immediately removed from storage. Storage space is reclaimed only after a BUILD job processes the marked rows.

Storage usage does not drop immediately after a DELETE. To reclaim space, trigger a BUILD job:

• Automatically: A BUILD job runs automatically when the cluster meets specific conditions. See Automatic BUILD.

• Manually: Trigger a BUILD job at any time. See Manual BUILD.