DELETE

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

Examples

Create a table named 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 where col1=2 from the tbl1 table, where col1 is the primary key of the tbl1 table.

  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 set
  

• Delete rows directly from a subquery.

  obclient> DELETE FROM (SELECT * FROM tbl1);
  Query OK, 4 rows affected
  
  obclient> SELECT * FROM tbl1;
  Empty set
  

• Delete rows with the RETURNING clause.

  obclient> DELETE FROM tbl1 RETURNING col1;
  +----+
  | COL1 |
  +----+
  |  1 |
  |  2 |
  |  3 |
  |  4 |
  +----+
  4 rows in set
  
  obclient>SELECT * FROM t1;
  Empty set