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 tables, DELETE can be performed only in the following two scenarios:
  • Scenarios with primary key constraints.
  • Scenario where the execution plan can be pushed down.
• For column-store tables, the RETURNING clause is currently not supported.

Syntax

1
2
3
4
5

[ 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

  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.

• using_list

  Specifies the USING clause.

• condition

  Specifies 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

  Specifies 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

  Specifies a name to use for a returned column.

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

Examples

Create a range partitioned table customer_address_bak:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

DROP TABLE IF EXISTS customer_address_bak;
CREATE TABLE customer_address_bak
(
    ca_address_sk       INTEGER                  NOT NULL   ,
    ca_address_id       CHARACTER(16)            NOT NULL   ,
    ca_street_number    CHARACTER(10)                       ,
    ca_street_name      CHARACTER varying(60)               ,
    ca_street_type      CHARACTER(15)                       ,
    ca_suite_number     CHARACTER(10)                    
)
DISTRIBUTE BY HASH (ca_address_sk)
PARTITION BY RANGE(ca_address_sk)
(
        PARTITION P1 VALUES LESS THAN(2450815),
        PARTITION P2 VALUES LESS THAN(2451179),
        PARTITION P3 VALUES LESS THAN(2451544),
        PARTITION P4 VALUES LESS THAN(MAXVALUE)
);

Delete employees whose ca_address_sk is less than 14888 in the tpcds.customer_address_bak table:

1

DELETE FROM customer_address_bak WHERE ca_address_sk < 14888;

Delete the employees whose ca_address_sk is 14891, 14893, and 14895 from tpcds. customer_address_bak:

1

DELETE FROM customer_address_bak WHERE ca_address_sk in (14891,14893,14895);

Delete all data in customer_address_bak:

1

DELETE FROM customer_address_bak;