Description
You can execute the DELETE statement to delete rows that meet the specified conditions from one or more tables.
Syntax
DELETE [hint_options] [FROM] table_factor
[WHERE where_condition]
[{ RETURNING | RETURN } returning_exprs [into_clause]]
table_factor:
{tbl_name | table_subquery | '(' table_reference ')' }
where_condition:
expression
returning_exprs:
projection [, ...]
into_clause:
{ INTO into_var_list | BULK COLLECT INTO into_var_list}
into_var_list:
{ USER_VARIABLE | ref_name } [, ...]
Parameters
| Parameter | Description |
|---|---|
| hint_options | The hint. |
| table_factor | The name of the table from which you want to delete rows. You can specify a base table, an updatable view, or a special subquery. |
| where_condition | The filter conditions. The system deletes rows from tables that meet the specified conditions. |
| returning_exprs | Return the projection that is defined before rows are deleted. |
| into_clause | Insert the projection that is defined before rows are deleted into the specified table. |
Notice
A special subquery is similar to a subquery in an updatable view. A special subquery cannot include complex operators, such as GROUP BY, DISTINCT, and WINDOW FUNCTION.
Examples
The following statements define a sample table and inserts data into the table:
OceanBase(admin@test)>create table t1(c1 int primary key, c2 int);
Query OK, 0 rows affected (0.16 sec)
OceanBase(admin@test)>select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set (0.06 sec)
- Delete rows from a table: Delete the rows where the value in the c1 column is 2. The c1 column is the primary key of the t1 table.
OceanBase(admin@test)>DELETE FROM t1 WHERE c1 = 2;
Query OK, 1 row affected (0.02 sec)
OceanBase(admin@test)>select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 3 | 3 |
| 4 | 4 |
+----+------+
3 rows in set (0.01 sec)
- Delete rows from a table: Use a subquery to delete rows.
OceanBase(admin@test)>DELETE FROM (SELECT * FROM t1);
Query OK, 4 rows affected (0.04 sec)
OceanBase(admin@test)>select * from t1;
Empty set (0.01 sec)
- Delete rows from a table: Execute a statement that includes the RETURNING clause.
OceanBase(admin@test)>DELETE FROM t1 RETURNING c1;
+----+
| C1 |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.01 sec)
OceanBase(admin@test)>select * from t1;
Empty set (0.01 sec)