DELETE|V4.4.2| docs|Distributed Database

Purpose

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

Limitations and considerations

You cannot directly delete data 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-compatible 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

Parameter	Description
single_table_delete_syntax	The SQL syntax for deleting rows from a single table.
hint_options	Optional. Specifies the `hint` option. For more information about hints, see Optimizer Hint.
IGNORE	Optional. Ignores errors that can be ignored during row deletion. This keyword is supported only in the current version, but its functionality is not effective.
table_name	The name of the table from which to delete data.
PARTITION (partition_name_list)	Optional. Specifies the list of partition names to delete data from. If deleting data from multiple partitions, separate the partition names with commas. For more information about partition names, see partition_name below.
WHERE where_condition_list	Optional. Specifies the list of filter conditions for deleting specific rows. If the `WHERE` clause is omitted, all rows are deleted. For more information about filter conditions, see where_condition below.
ORDER BY order_expression_list	Optional. Specifies the list of sort keys for the rows to delete. Typically used with the `LIMIT` clause. For more information about sorting, see order_expression below.
LIMIT row_count	Optional. Specifies 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. Notice If an alias is declared for a table in the `table_references` clause, the alias must be used 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 select from when performing a multi-table delete. For more information, see table_reference below. Notice Table aliases can only be declared in the `table_references` clause. Aliases can be referenced elsewhere, but cannot be declared there.

partition_name

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

Here is an example:

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

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

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 used to filter the rows to be deleted.

Here is an example:

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

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, default) or descending order (DESC).

Here is an example:

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

Delete the first row (i.e., the largest row) in the tbl1 table after sorting 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
```

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 identifies a table, which can be specified in different ways. The following are the different ways:

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

Here is an example:

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

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

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

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

Delete the data in the p0 partition of the tbl3 table that matches the data in the tbl2 table where tbl2.col1 = tbl3.col1.
```
DELETE tbl3 FROM tbl2,tbl3 PARTITION(p0) WHERE tbl2.col1 = tbl3.col1;
```

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

Here is an example:

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

Here is an example:

Delete the 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;
```

Examples

Delete data from an updatable view.

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

Create a view named v1.

CREATE VIEW v1
    AS SELECT * FROM test_tbl1;

Query data from the v1 view.

SELECT * FROM v1;

The returned result is as follows:

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

Delete the value where col1 = 1 in the v1 view.
```
DELETE FROM v1
    WHERE col1 = 1;
```

Query data from the v1 view again.

SELECT * FROM v1;

The returned result is as follows:

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

References

Delete data

DELETE

Purpose

Limitations and considerations

Privilege requirements

Syntax

Parameters

Notice

Notice

partition_name

where_condition

order_expression

table_reference

Examples

References