AnalyticDB:DELETE

Usage notes

• The table on which you execute the DELETE statement must have a primary key.

• You cannot use the alias of a table to execute the DELETE statement.

• If you want to delete data from an entire table or partition, we recommend that you use the TRUNCATE TABLE or TRUNCATE TABLE PARTITION statement, instead of the DELETE statement. For more information, see TRUNCATE TABLE.

• When you delete data from multiple tables, you can execute an SQL statement only on a single table.

Syntax

When you delete data from a single table, you can query data from the table and delete the data that meets the conditions specified in the WHERE clause. When you delete data from multiple tables, you can execute an SQL statement to query data from multiple joined tables and delete the data that meets the conditions specified in the WHERE clause from one table.

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 the data whose name column is Alex from the customer table.

  DELETE FROM customer WHERE customer_name='Alex';                  

• Delete the data whose age column is less than 18 from the customer table.

  DELETE FROM customer WHERE age<18;                

• Query data from joined tables based on the id column of the customer table and the id column of the orders table, and delete the data whose age column is 18 from the customer table.

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

Common errors and troubleshooting