All Products
Search
Document Center

DELETE

Last Updated: Jun 18, 2021

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)