DELETE

Purpose

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

Limitations and considerations

You cannot directly delete from a subquery in a multi-table or single-table delete statement. For example, you cannot execute the DELETE FROM (SELECT * FROM t1); statement.

Privilege requirements

To execute the DELETE statement, the current user must have the DELETE privilege on the target table. For more information about OceanBase Database privileges, see Privilege types in MySQL mode.

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

Parameters

partition_name

partition_name: specifies the name of the partition from which data is to be deleted.

Here is an example:

1. Create a table named 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 the data in the p0 and p1 partitions of the tbl1 table.

   SELECT * FROM tbl1 PARTITION(p0, p1);
   

   The result is as follows:

   +------+------+
   | col1 | col2 |
   +------+------+
   |    5 | A5   |
   |    1 | A1   |
   |    6 | A6   |
   +------+------+
   3 rows in set
   

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

   DELETE FROM tbl1 PARTITION(p0, p1);
   

   The result is as follows:

   Query OK, 3 rows affected
   

where_condition

expression: specifies the condition expression that can be used to filter the rows to be deleted.

Here is an example:

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

   SELECT * FROM tbl1 WHERE col1 = 2;
   

   The result is as follows:

   +------+------+
   | col1 | col2 |
   +------+------+
   |    2 | A2   |
   +------+------+
   1 row in set
   

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

   DELETE FROM tbl1 WHERE col1 = 2;
   

   The result is as follows:

   Query OK, 1 row affected
   

order_expression

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

Here is an example:

1. View the data in the tbl1 table.

   SELECT * FROM tbl1;
   

   The result is as follows:

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

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

   DELETE FROM tbl1
       ORDER BY col1 DESC
       LIMIT 1;
   

   The result is as follows:

   Query OK, 1 row affected
   

3. View the data in the tbl1 table again.

   SELECT * FROM tbl1;
   

   The result is as follows:

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

table_reference

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

• table_name: specifies the name of the table and can also be used to delete data from a partition.

  Here is an example:

  1. Create a table named 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 named 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 the data from 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 the data from the p0 partition of the tbl3 table where tbl2.col1 = tbl3.col1.

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

• joined_table: specifies the joined table, which can be specified in multiple ways and connected using JOIN.

  Here is an example:

  Delete the records from 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 the subquery table.

  Here is an example:

  Delete the rows from 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;
  

Examples

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. View the data in v1.

   SELECT * FROM v1;
   

   The returned result is as follows:

   +------+------+
   | col1 | col2 |
   +------+------+
   |    1 |    1 |
   |    2 |    2 |
   |    3 |    3 |
   +------+------+
   3 rows in set
   

4. Delete the value where col1 = 1 in the view v1.

   DELETE FROM v1
       WHERE col1 = 1;
   

5. View the data in v1 again.

   SELECT * FROM v1;
   

   The returned result is as follows:

   +------+------+
   | col1 | col2 |
   +------+------+
   |    2 |    2 |
   |    3 |    3 |
   +------+------+
   2 rows in set
   

References

Delete data