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
Neither multi-table deletion nor single-table deletion supports deleting directly from a subquery, for example, DELETE FROM (SELECT * FROM t1);.
Privilege requirements
To execute a DELETE statement, the current user must have the DELETE privilege on the target table. For more information about OceanBase AI Database privileges, see Permission 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
Parameters
Parameters |
Description |
|---|---|
| single_table_delete_syntax | SQL syntax for deleting rows from a single table. |
| hint_options | Optional. Specifies the hint Options. For more information about hints, see Optimizer hint. |
| IGNORE | Optional. Ignores ignorable errors during row deletion. This keyword syntax is supported only in the current version, but the feature is not effective. |
| table_name | Specify the name of the table from which you want to delete data. |
| PARTITION (partition_name_list) | Optional. Specifies the list of partition names whose data is to be deleted. Separate multiple partition names with commas. For more information about deleting partition data, see partition_name. |
| WHERE where_condition_list | Optional. Specifies the filter conditions for deleting specific rows. If the WHERE clause is not specified, all rows are deleted. For more information about deleting data based on specific conditions, see the where_condition. |
| ORDER BY order_expression_list | Optional. Specifies the list of sort keys for the rows to be deleted. Typically used with LIMIT can be used together. For details on the deletion order, see order_expression. |
| LIMIT row_count | Optional. Specifies the maximum number of rows to delete. row_count can only be an integer. |
| multiple_table_delete_syntax | SQL syntax for deleting rows from multiple tables. |
| table_name_list | Specifies the list of tables from which data is to be deleted.
NoticeIf an alias is declared for a table in the |
| table_references | Specify the table or tables from which data is to be deleted, that is, the sequence of tables to select when deleting multiple tables. For more information, see table_reference below.
NoticeTable aliases can only be declared in the |
partition_name
partition_name: The name of the partition from which data needs to be deleted.
Example:
Create table
tbl1and 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 partitions
p0andp1of tabletbl1.SELECT * FROM tbl1 PARTITION(p0, p1);The return result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 5 | A5 | | 1 | A1 | | 6 | A6 | +------+------+ 3 rows in setDelete the data in partitions
p0andp1of tabletbl1.DELETE FROM tbl1 PARTITION(p0, p1);The return result is as follows:
Query OK, 3 rows affected
where_condition
expression: Specifies the conditional expression that can be used to filter the rows to be deleted.
Example:
Query the data where
col1 = 2in tabletbl1.SELECT * FROM tbl1 WHERE col1 = 2;The return result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 2 | A2 | +------+------+ 1 row in setDelete the data where
col1 = 2from tabletbl1.DELETE FROM tbl1 WHERE col1 = 2;The return result is as follows:
Query OK, 1 row affected
order_expression
expression [ASC \| DESC]: Specifies the expression to sort by in ascending order (ASC, the default) or descending order (DESC).
Example:
View the data in table
tbl1.SELECT * FROM tbl1;The return result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 7 | A7 | | 3 | A3 | | 8 | A8 | | 4 | A4 | | 9 | A9 | +------+------+ 5 rows in setDelete the first row after descending order sorting by column
col1from tabletbl1(that is, delete the largest row after sorting).DELETE FROM tbl1 ORDER BY col1 DESC LIMIT 1;The return result is as follows:
Query OK, 1 row affectedView the data in table
tbl1again.SELECT * FROM tbl1;The return 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 used to identify the table in an SQL statement, indicating different ways of representing it. Specifically:
table_name: the name of the table. You can also specify a partition to delete data from.Example:
Create table
tbl2and 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 table
tbl3and 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 that satisfies
tbl2.col1 = tbl3.col1 AND tbl2.col2 = tbl3.col2from tablestbl2andtbl3.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;Delete the data in partition
p0of tabletbl3and the data in tabletbl2that satisfiestbl2.col1 = tbl3.col1.DELETE tbl3 FROM tbl2,tbl3 PARTITION(p0) WHERE tbl2.col1 = tbl3.col1;
joined_table: the table to join. Multiple tables can be specified and joined usingJOIN.Example:
Delete records from
tbl2wherecol1is equal tocol1intbl3andcol1is greater than1.DELETE tbl2 FROM tbl2 INNER JOIN tbl3 ON tbl2.col1 = tbl3.col1 WHERE tbl2.col1 > 1;table_subquery: the table of the subquery.Example:
Delete rows from tables
tbl2andtbl3wheretbl3.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
Performs a DELETE operation on an updatable view.
Create the table
test_tbl1and 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 view
v1.CREATE VIEW v1 AS SELECT * FROM test_tbl1;View the data in
v1.SELECT * FROM v1;The return result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +------+------+ 3 rows in setDelete the value where
col1 = 1from viewv1.DELETE FROM v1 WHERE col1 = 1;View the data in
v1again.SELECT * FROM v1;The return result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 2 | 2 | | 3 | 3 | +------+------+ 2 rows in set