• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. DELETE

DELETE

Function

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.

Precautions

  • 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.
  • For replication table, adding key constraints. Without keys, an error will occur during the delete operation.
  • For column-store tables, the RETURNING clause is currently not supported.
  • For column-store tables and replicated tables, DELETE and UPDATE are not supported.

Syntax

[ 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 compliant with the naming convention.

  • 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 compliant with the naming convention.

Example

-- 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;