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 } [, ...]
参数解释
|
参数 |
描述 |
|---|---|
|
hint_options |
指定 Hint 选项。 |
|
table_factor |
指定需要删除的表名（基表、可更新视图、特殊子查询）。 |
|
where_condition |
删除的表需要满足的过滤条件。 |
|
returning_exprs |
返回删除数据前的投影列。 |
|
into_clause |
将删除数据前的投影列插入到指定列表。 |
注意
特殊子查询指的类似于可更新视图对应的子查询，这类子查询不应该包含复杂的算子（比如 GROUP BY/DISTINCT/WINDOW FUNCTION等）
示例
创建表 t1 并插入数据：
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)- 单表删除：直接操作子查询进去删除操作。
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)