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.

Limits

  • 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.

Syntax

The DELETE statement uses the following syntax:

DELETE FROM table_name [ * ] [ [ AS ] alias ]
    [ WHERE condition  ]
The following table describes the parameters in the syntax.
Parameter Description
alias The substitute name for the table from which to delete rows.
condition The condition that rows to be deleted must meet.

Example

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.