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

Notice

A special subquery is similar to a subquery that defines an updatable view. These subqueries must not contain complex operators (such as GROUP BY, DISTINCT, and WINDOW FUNCTION).

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