Purpose
This statement is used to delete rows (data) that meet the specified conditions from a table.
Privilege requirements
To execute the DELETE statement, the current user must have the DELETE system privilege. For more information about OceanBase Database privileges, see Privilege classification in Oracle-compatible mode.
Syntax
DELETE [hint_options] FROM table_factor
[WHERE where_expression]
[{RETURNING | RETURE } returning_exprs [ into_clause ]]
[LOG ERRORS [INTO] table_name REJECT LIMIT { integer | UNLIMITED }]
table_factor:
{table_name | table_subquery | '(' table_reference ')' }
returning_exprs:
projection_col_name [,projection_col_name ...]
into_clause:
[BULK COLLECT] INTO into_var_list
into_var_list:
{ USER_VARIABLE | ref_name } [, { USER_VARIABLE | ref_name }...]
Parameters
| Parameter | Description |
|---|---|
DELETE |
The keyword for executing the delete operation. |
hint_options |
Optional hint options for optimizing the query execution plan. |
| table_factor | Specifies the name of the table (base table, updatable view, or special subquery) from which data needs to be deleted.
NoticeA special subquery is similar to an updatable view's corresponding subquery and should not contain complex operators (such as |
table_name |
The name of the target table from which data needs to be deleted. |
table_subquery |
Optional subquery specifying the source of the records to be deleted. |
table_reference |
The table reference, which can be a table name or other form of table expression. |
WHERE where_expression |
Optional condition specifying the records to be deleted. |
RETURNING |
Optional keyword indicating that specified column values should be returned after the delete operation. |
RETURN |
Equivalent to RETURNING, another way to write it. |
returning_exprs |
A list of column names to be returned, which can include multiple column names. |
into_clause |
Optional statement for storing the returned values into variables. |
BULK COLLECT |
Indicates that the returned results should be stored in variables using bulk collection. |
into_var_list |
A list of variables for storing the returned values, which can be user-defined variables or reference names. |
LOG ERRORS |
Optional statement indicating that errors should be recorded during the delete operation. |
INTO |
Specifies the name of the target table for error records. |
REJECT LIMIT |
Specifies the maximum number of records allowed when an error occurs, which can be an integer or unlimited. |
Examples
Create a table tbl1 and insert data.
obclient> CREATE TABLE tbl1(col1 INT PRIMARY KEY, col2 INT);
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES(1,1),(2,2),(3,3),(4,4);
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM tbl1;
+------+------+
| COL1 | COL2 |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+------+------+
4 rows in set
Delete rows from a single table: delete rows where
col1=2, wherecol1is the Primary Key of tabletbl1.obclient>DELETE FROM tbl1 WHERE col1 = 2; Query OK, 1 row affected obclient>SELECT * FROM tbl1; +----+------+ | COL1 | COL2 | +----+------+ | 1 | 1 | | 3 | 3 | | 4 | 4 | +----+------+ 3 rows in setDelete rows from a single table: directly operate on the subquery to perform the delete operation.
obclient> DELETE FROM (SELECT * FROM tbl1); Query OK, 4 rows affected obclient> SELECT * FROM tbl1; Empty setDelete rows from a single table: include the
RETURNINGclause.obclient> DELETE FROM tbl1 RETURNING col1; +----+ | COL1 | +----+ | 1 | | 2 | | 3 | | 4 | +----+ 4 rows in set obclient>SELECT * FROM t1; Empty set