DELETE

Purpose

You can use this statement to delete rows that meet the conditions from one or more tables.

Syntax

DELETE [hint_options] [FROM] table_factor [ WHERE where_condition ]
    [{ RETURNING | RETURN } returning_exprs [ into_clause ]]

table_factor:
    {table_name | table_subquery | '(' table_reference ')' }

where_condition:
    expression

returning_exprs:
    projection_col_name [,projection_col_name ...]

into_clause:
    { INTO into_var_list | 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 into it.

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

• Single-table delete: Delete rows where col1 = 2 from the tbl1 table. The col1 column is the primary key.

  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
  

• Single-table delete: Use a subquery to delete data from a table.

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

• Single-table delete: Delete data from a table, with the RETURNING clause specified in the statement.

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