You can execute the DELETE statement to delete rows that meet specific conditions from a table. This topic describes how to use the DELETE statement in Hologres.
- You cannot execute the DELETE statement to delete rows from a parent partitioned table if its child partitioned tables contain data. To delete rows of the parent partitioned table, you must delete the rows of its child partitioned tables first.
- To delete all rows from a table, we recommend that you use the TRUNCATE statement, which is more efficient. For more information, see TRUNCATE.
- We recommend that you use fixed plans to optimize execution efficiency. For more information, see DELETE statements.
The DELETE statement uses the following syntax:
The following table describes the parameters in the syntax.
DELETE FROM table_name [ * ] [ [ AS ] alias ] [ WHERE condition ]
|alias||The substitute name for the table from which to delete rows.|
|condition||The condition that rows to be deleted must meet.|
The following example shows how to use the DELETE statement in Hologres:
CREATE TABLE delete_test ( id INT PRIMARY KEY, a INT, b text ); INSERT INTO delete_test VALUES (1, 10, 'a'), (2, 30, 'b'), (3, 50, ''), (4, 70, null); DELETE FROM delete_test AS dt WHERE dt.a = 10; DELETE FROM delete_test AS dt WHERE dt.b is null; DELETE FROM delete_test AS dt WHERE dt.b='';
For more information about the DELETE statement, see DELETE.