DELETE

Description

This statement is used to delete rows (data) from a table that meet certain conditions. It supports both single-table and multi-table deletion.

Limitations and considerations

Neither multi-table deletion nor single-table deletion supports direct deletion from a subquery, for example, DELETE FROM (SELECT * FROM t1);.

Privilege requirements

To execute the DELETE statement, the current user must have the DELETE privilege on the table to be operated. For more information about seekdb privileges, see Privilege classification.

Syntax

single_table_delete_syntax:
    DELETE [hint_options] [IGNORE]
        FROM table_name
        [PARTITION (partition_name_list)]
        [WHERE where_condition_list]
        [ORDER BY order_expression_list]
        [LIMIT row_count];

partition_name_list:
    partition_name [, partition_name ...]

where_condition_list:
    where_condition [, where_condition ...]

where_condition:
    expression

order_expression_list:
    order_expression [, order_expression ...]

order_expression:
    expression [ASC | DESC]

multiple_table_delete_syntax:
    DELETE [hint_options]
        table_name_list
        FROM table_references
        [WHERE where_condition_list];
Or:
    DELETE [hint_options]
        FROM table_name_list
        USING table_references
        [WHERE where_condition_list];

table_name_list:
    table_name[.*] [, table_name[.*] ...]

table_references:
    table_reference [, table_reference ...]

table_reference:
    table_name
    | joined_table
    | table_subquery

Parameter description

Parameter	Description
single_table_delete_syntax	The SQL syntax for deleting rows from a single table.
hint_options	An optional parameter for specifying the hint option. For more information about hints, see Optimizer Hint.
IGNORE	An optional parameter for ignoring errors that can be ignored during row deletion. This keyword is supported only in the current version, but its functionality does not take effect.
table_name	The name of the table from which to delete data.
PARTITION (partition_name_list)	An optional parameter for specifying the list of partition names from which to delete data. If multiple partitions are to be deleted, the partition names are separated by commas. For more information about deleting partition data, see partition_name.
WHERE where_condition_list	An optional parameter for specifying the list of filter conditions for deleting specific rows. If the WHERE clause is omitted, all rows are deleted. For more information about deleting data based on specific conditions, see where_condition.
ORDER BY order_expression_list	An optional parameter for specifying the list of sort keys for the rows to be deleted. This is typically used with the LIMIT clause. For more information about the deletion order, see order_expression.
LIMIT row_count	An optional parameter for specifying the maximum number of rows to delete. The value of row_count must be an integer.
multiple_table_delete_syntax	The SQL syntax for deleting rows from multiple tables.
table_name_list	The list of tables from which to delete data. NoticeIf an alias is declared for a table in the table_references clause, you must use the alias when referencing the table in the table_name_list clause.
table_references	The table or combination of tables from which to delete data, i.e., the sequence of tables to be selected when performing multi-table deletion. For more information, see table_reference. NoticeTable aliases can only be declared in the table_references clause. Aliases can be referenced elsewhere, but cannot be declared.

partition_name

partition_name：specifies the name of the partition from which to delete data.

Here is an example:

1. Create a table tbl1 and insert test data.

   CREATE TABLE tbl1(col1 INT, col2 VARCHAR(20))
       PARTITION BY HASH(col1) PARTITIONS 5;

   INSERT INTO tbl1 VALUES(1, 'A1'),(2, 'A2'),(3, 'A3'),(4, 'A4'),(5, 'A5'),(6, 'A6'),(7, 'A7'),(8, 'A8'),(9, 'A9');

2. View data in the p0 and p1 partitions of the tbl1 table.

   SELECT * FROM tbl1 PARTITION(p0, p1);

   The returned result is as follows:

   +------+------+
   | col1 | col2 |
   +------+------+
   |    5 | A5   |
   |    1 | A1   |
   |    6 | A6   |
   +------+------+
   3 rows in set (0.002 sec)

3. Delete data in the p0 and p1 partitions of the tbl1 table.

   DELETE FROM tbl1 PARTITION(p0, p1);

   The returned result is as follows:

   Query OK, 3 rows affected

where_condition

expression：specifies the condition expression for filtering the rows to be deleted.

Here is an example:

1. View data in the tbl1 table where col1 = 2.

   SELECT * FROM tbl1 WHERE col1 = 2;

   The returned result is as follows:

   +------+------+
   | col1 | col2 |
   +------+------+
   |    2 | A2   |
   +------+------+
   1 row in set (0.001 sec)

2. Delete data in the tbl1 table where col1 = 2.

   DELETE FROM tbl1 WHERE col1 = 2;

   The returned result is as follows:

   Query OK, 1 row affected (0.001 sec)

order_expression

expression [ASC \| DESC]：specifies the expression to be sorted in ascending order (ASC, default) or descending order (DESC).

Here is an example:

1. View data in the tbl1 table.

   SELECT * FROM tbl1;

   The returned result is as follows:

   +------+------+
   | col1 | col2 |
   +------+------+
   |    7 | A7   |
   |    3 | A3   |
   |    8 | A8   |
   |    4 | A4   |
   |    9 | A9   |
   +------+------+
   5 rows in set (0.003 sec)

2. Delete the first row (i.e., the largest row) after sorting the tbl1 table in descending order by the col1 column.

   DELETE FROM tbl1
       ORDER BY col1 DESC
       LIMIT 1;

   The returned result is as follows:

   Query OK, 1 row affected (0.003 sec)

3. View data in the tbl1 table again.

   SELECT * FROM tbl1;

   The returned result is as follows:

   +------+------+
   | col1 | col2 |
   +------+------+
   |    7 | A7   |
   |    3 | A3   |
   |    8 | A8   |
   |    4 | A4   |
   +------+------+
   4 rows in set (0.003 sec)

table_reference

table_reference refers to the part of an SQL statement that identifies a table, which can be specified in different ways. The following describes the different ways:

• table_name：specifies the name of the table, and you can also specify a partition to delete data.

  Here is an example:

  1. Create a table tbl2 and insert test data.

     CREATE TABLE tbl2(col1 INT PRIMARY KEY, col2 INT);

     INSERT INTO tbl2 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);

  2. Create a table tbl3 and insert test data.

     CREATE TABLE tbl3(col1 INT PRIMARY KEY, col2 INT) PARTITION BY KEY(col1) PARTITIONS 4;

     INSERT INTO tbl3 VALUES(1,11),(2,22),(3,3),(6,66),(7,77),(8,88);

  3. Delete data in the tbl2 and tbl3 tables where tbl2.col1 = tbl3.col1 AND tbl2.col2 = tbl3.col2.

     DELETE tbl2, tbl3
         FROM tbl2, tbl3
         WHERE tbl2.col1 = tbl3.col1
         AND tbl2.col2 = tbl3.col2;

     or

     DELETE FROM tbl2, tbl3
         USING tbl2, tbl3
         WHERE tbl2.col1 = tbl3.col1
         AND tbl2.col2 = tbl3.col2;

  4. Delete data in the p0 partition of the tbl3 table that satisfies tbl2.col1 = tbl3.col1 in the tbl2 table.

     DELETE tbl3 FROM tbl2,tbl3 PARTITION(p0) WHERE tbl2.col1 = tbl3.col1;

• joined_table：specifies a joined table, which can specify multiple tables and use JOIN to join them.

  Here is an example:

  Delete records in the tbl2 table where col1 is equal to col1 in the tbl3 table and col1 is greater than 1.

  DELETE tbl2
      FROM tbl2 INNER JOIN tbl3 ON tbl2.col1 = tbl3.col1
      WHERE tbl2.col1 > 1;

• table_subquery：specifies a table that is derived from a subquery.

  Here is an example:

  Delete rows in the tbl2 and tbl3 tables where tbl3.col1 < 2 AND tbl2.col1 = t3.col1 using a subquery.

  DELETE FROM t2, t3
      USING tbl2 t2, (SELECT * FROM tbl3 WHERE tbl3.col1 < 2) t3
      WHERE t2.col1 = t3.col1;

Example

Delete data from an updatable view.

1. Create a table named test_tbl1 and insert test data.

   CREATE TABLE test_tbl1(col1 INT PRIMARY KEY, col2 INT);

   INSERT INTO test_tbl1 VALUES(1,1),(2,2),(3,3);

2. Create a view named v1.

   CREATE VIEW v1
       AS SELECT * FROM test_tbl1;

3. Query data from the v1 view.

   SELECT * FROM v1;

   The query result is as follows:

   +------+------+
   | col1 | col2 |
   +------+------+
   |    1 |    1 |
   |    2 |    2 |
   |    3 |    3 |
   +------+------+
   3 rows in set (0.002 sec)

4. Delete the value of col1 = 1 from the v1 view.

   DELETE FROM v1
       WHERE col1 = 1;

5. Query data from the v1 view again.

   SELECT * FROM v1;

   The query result is as follows:

   +------+------+
   | col1 | col2 |
   +------+------+
   |    2 |    2 |
   |    3 |    3 |
   +------+------+
   2 rows in set (0.003 sec)

References

Delete data