OceanBase DELETE

描述

該語句用來刪除表中符合條件的行，包括單表刪除及多表刪除兩種方式。

格式

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 } [, ...]
 

參數(shù)解釋

注意 
特殊子查詢指的類似于可更新視圖對(duì)應(yīng)的子查詢，這類子查詢不應(yīng)該包含復(fù)雜的算子（比如 ?GROUP BY?/?DISTINCT?/?WINDOW FUNCTION?等）

示例

創(chuàng)建表 t1 并插入數(shù)據(jù)：

obclient>CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected (0.16 sec)

obclient>INSERT INTO t1 VALUES(1,1);
Query OK, 1 row affected (0.00 sec)

obclient>INSERT INTO t1 VALUES(2,2);
Query OK, 1 row affected (0.00 sec)

obclient>INSERT INTO t1 VALUES(3,3);
Query OK, 1 row affected (0.00 sec)

obclient>INSERT INTO t1 VALUES(4,4);
Query OK, 1 row affected (0.00 sec)

obclient>SELECT * FROM t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.06 sec)

• 單表刪除：刪除 “c1=2” 的行，其中 c1 列為表 t1 中的 Primary Key。

obclient>DELETE FROM t1 WHERE c1 = 2;
Query OK, 1 row affected (0.02 sec)

obclient>SELECT * FROM t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  3 |    3 |
|  4 |    4 |
+----+------+
3 rows in set (0.01 sec)

• 單表刪除：直接操作子查詢進(jìn)去刪除操作。

obclient>DELETE FROM (SELECT * FROM t1);
Query OK, 4 rows affected (0.04 sec)

obclient>SELECT * FROM t1;
Empty set (0.01 sec)

• 單表刪除：包含 ?RETURNING? 子句。

obclient>DELETE FROM t1 RETURNING c1;
+----+
| C1 |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.01 sec)

obclient>SELECT * FROM t1;
Empty set (0.01 sec)