Help Center > > Developer Guide> SQL Reference> SQL Syntax> DELETE


Updated at: Mar 13, 2020 GMT+08:00


DELETE deletes rows that satisfy the WHERE clause from the specified table. If the WHERE clause does not exist, all rows in the table will be deleted. The result is a valid, but an empty table.


  • You must have the DELETE permission on the table to delete from it, as well as the SELECT permission for any table in the USING clause or whose values are read in the condition.
  • DELETE can be used for row-store tables if they have primary key constraints or if the execution plan can be pushed down.
  • DELETE can be used for column-store tables only if the execution plan can be pushed down.
  • For column-store tables, the RETURNING clause is currently not supported.


[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ USING using_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING { * | { output_expr [ [ AS ] output_name ] } [, ...] } ];

Parameter Description

  • WITH [ RECURSIVE ] with_query [, ...]

    The WITH clause allows you to specify one or more subqueries that can be referenced by name in the primary query, equal to temporary table.

    If RECURSIVE is specified, it allows a SELECT subquery to reference itself by name.

    The with_query detailed format is as follows:

    with_query_name [ ( column_name [, ...] ) ] AS

    ( {select | values | insert | update | delete} )

    -- with_query_name specifies the name of the result set generated by a subquery. Such names can be used to access the result sets of

    subqueries in a query.

    column_name specifies the column name displayed in the subquery result set.

    Each subquery can be a SELECT, VALUES, INSERT, UPDATE or DELETE statement.

  • ONLY

    If ONLY is specified, only that table is deleted. If ONLY is not specified, this table and all its sub-tables are deleted.

  • table_name

    Specifies the name (optionally schema-qualified) of a target table.

    Value range: An existing table name.

  • alias

    Specifies the alias for the target table.

    Value range: A string. It must comply with the naming convention rule.

  • using_list

    using clause

  • condition

    An expression that returns a value of type Boolean. Only rows for which this expression returns true will be deleted.

  • WHERE CURRENT OF cursor_name

    Not supported currently. Only syntax interface is provided.

  • output_expr

    An expression to be computed and returned by the DELETE command after each row is deleted. The expression can use any column names of the table. Write * to return all columns.

  • output_name

    A name to use for a returned column.

    Value range: A string. It must comply with the naming convention rule.


-- Create the tpcds.customer_address_bak table:
CREATE TABLE tpcds.customer_address_bak AS TABLE tpcds.customer_address;

-- Delete employees whose ca_address_sk is less than 14888 in the tpcds.customer_address_bak table:
DELETE FROM tpcds.customer_address_bak WHERE ca_address_sk < 14888;

-- Delete all data in the tpcds.customer_address_bak table:
DELETE FROM tpcds.customer_address_bak;

-- Delete the tpcds.customer_address_bak table:
DROP TABLE tpcds.customer_address_bak;

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?

Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel