---
title: "DELETE - OceanBase 数据库 V4.2.0 | OceanBase 文档中心"
description: "DELETE 描述 该语句用来删除表中符合条件的行，包括单表删除及多表删除两种方式。 无论是多表删除还是单表删除都不支持直接对子查询进行删除操作，例如 DELETE FROM (SELECT * FROM t1); 。 语法 Single-Table-Delete Syntax: DELETE [hint_optio…"
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 4.2.0

# DELETE

更新时间：2023-09-18 15:46:06

[编辑](https://github.com/oceanbase/oceanbase-doc/edit/V4.2.0/zh-CN/700.reference/500.sql-reference/100.sql-syntax/200.common-tenant-of-mysql-mode/600.sql-statement-of-mysql-mode/3200.delete-of-mysql-mode.md)  

## 描述

该语句用来删除表中符合条件的行，包括单表删除及多表删除两种方式。

无论是多表删除还是单表删除都不支持直接对子查询进行删除操作，例如 `DELETE FROM (SELECT * FROM t1);`。

## 语法

```sql
Single-Table-Delete Syntax:
    DELETE [hint_options] FROM table_name
    [PARTITION (partition_name,...)]
    [WHERE where_condition]
    [ORDER BY order_expression_list]
    [LIMIT row_count]

Multiple-Table-Delete Syntax:
    DELETE [hint_options] table_name[.*] [, table_name[.*]] ...
    FROM table_references
    [WHERE where_condition]
Or:
    DELETE [hint_options] FROM table_name[.*] [, table_name[.*]] ...
    USING table_references
    [WHERE where_condition]

where_condition:
    expression

order_expression_list:
    order_expression [, order_expression ...]

order_expression:
    expression [ASC | DESC]

limit_row_count:
    INT_VALUE

table_references:
    {table_name | joined_table | table_subquery | select_with_parents} [, ...]

```

## 参数解释

| **参数** | **描述** |
| --- | --- |
| hint_options | 指定 `hint` 选项。 |
| table_name | 指定需要删除的表名。 |
| partition_name | 需要删除表的对应分区名。 |
| where_condition | 删除的表需要满足的过滤条件。 |
| order_expression_list | 删除的表的排序键列表。 |
| row_count | 指定待删除的表的行数。指定的值只能为整数。 |
| table_references | 多表删除时指定的待选择的表序列。 |

## 示例

示例表及数据基于以下定义：

```sql
obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected
obclient> SELECT * FROM t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set

obclient> CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT) PARTITION BY KEY(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2   |
+----+------+
|  5 |    5 |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
4 rows in set

```

- 单表删除：删除 `c1=2` 的行，其中 `c1` 列为表 `t1` 中的 Primary Key。

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

  ```
 - 单表删除：删除表 `t1` 的按照 `c2` 列排序之后的第一行数据。

  ```sql
  obclient> DELETE FROM t1 ORDER BY c2 LIMIT 1;
  Query OK, 1 row affected

  obclient> SELECT * FROM t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  2 |    2 |
  |  3 |    3 |
  |  4 |    4 |
  +----+------+
  3 rows in set

  ```
 - 单表删除：执行删除表 `t2` 的 `p2` 分区的数据。

  ```sql
  obclient> DELETE FROM t2 PARTITION(p2);
  Query OK, 3 rows affected

  obclient> SELECT * FROM t2;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  5 |    5 |
  +----+------+
  1 row in set

  ```
 - 多表删除：删除 `t1`，`t2` 表中 `t1.c1 = t2.c1` 的数据。

  ```sql
  obclient> DELETE t1, t2 FROM t1, t2 WHERE t1.c1 = t2.c1;
  Query OK, 3 rows affected

  obclient> SELECT * FROM t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  4 |    4 |
  +----+------+
  1 row in set

  ```sql
  obclient> DELETE FROM t1, t2 USING t1,t2 WHERE t1.c1 = t2.c1;
  Query OK, 6 rows affected

  ```
 - 多表删除：删除 `t2` 表中的 `p2` 分区中和 `t1` 表中满足 `t1.c1 = t2.c1` 的数据。

  ```sql
  obclient> DELETE t2 FROM t1,t2 PARTITION(p2) WHERE t1.c1 = t2.c1;
  Query OK, 3 rows affected

  ```
 - 对可更新视图 `v` 进行删除操作。

  ```sql
  obclient> CREATE VIEW v AS SELECT * FROM t1;
  Query OK, 0 rows affected

  obclient> DELETE FROM v WHERE v.c1 = 1;
  Query OK, 1 row affected

  obclient> SELECT * FROM v;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  2 |    2 |
  |  3 |    3 |
  |  4 |    4 |
  +----+------+
  3 rows in set

  ```

 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线