如何使用DELETE删除表中行的数据-云原生大数据计算服务 MaxCompute(MaxCompute)-阿里云帮助中心

DELETE操作用于删除Transactional或Delta Table表中满足指定条件的单行或多行数据。

前提条件

执行DELETE、UPDATE操作前需要具备目标Transactional表或Delta Table表的读取表数据权限（Select）及更新表数据权限（Update）。授权操作请参见MaxCompute权限。

使用限制

DELETE、UPDATE功能及对应Transactional表、Delta Table表的使用限制如下：
说明
关于Transaction表和Delta Table表详细信息，详情请参见Transaction Table与Delta Table表参数。
- Delta Table表UPDATE语法不支持修改PK列。

注意事项

通过DELETE、UPDATE操作删除或更新表或分区内的数据时，注意事项如下：

如果需要对表中较少数据进行删除或更新操作，且操作和后续读数据的频率也不频繁，建议使用DELETE、UPDATE操作，并且在多次执行删除或更新操作之后，请合并表的Base文件和Delta文件，降低表的实际存储。更多信息，请参见合并Transactional表文件。
如果删除或更新行数较多（超过5%）并且操作不频繁，但后续对该表的读操作比较频繁，建议使用INSERT OVERWRITE或INSERT INTO操作。更多信息，请参见插入或覆写数据（INSERT INTO | INSERT OVERWRITE）。
例如，某业务场景为每次删除或更新10%的数据，一天更新10次。建议根据实际情况评估DELETE、UPDATE操作产生的费用及后续对读性能的消耗是否小于每次使用INSERT OVERWRITE或INSERT INTO操作产生的费用及后续对读性能的消耗，比较两种方式在具体场景中的效率，选择更优方案。
删除数据会生成Delta文件，所以删除数据不一定能降低存储，如果您希望通过DELETE操作删除数据来降低存储，请合并表的Base文件和Delta文件，降低表的实际存储。更多信息，请参见合并Transactional表文件。
MaxCompute会按照批处理方式执行DELETE、UPDATE作业，每一条语句都会使用资源并产生费用，建议您使用批量方式删除或更新数据。例如您通过Python脚本生成并提交了大量行级别更新作业，且每条语句只操作一行或者少量行数据，则每条语句都会产生与SQL扫描输入数据量对应的费用，并使用相应的计算资源，多条语句累加时将明显增加费用成本，降低系统效率。命令示例如下。
- 推荐方案：
```
UPDATE table1 SET col1= (SELECT value1 FROM table2 WHERE table1.id = table2.id AND table1.region = table2.region);
```
- 不推荐方案：
```
UPDATE table1 SET col1=1 WHERE id='2021063001' AND region='beijing';                 
UPDATE table1 SET col1=2 WHERE id='2021063002' AND region='beijing';
```

命令格式

DELETE FROM <table_name> [[AS] alias] [WHERE <condition>];

参数说明

参数	是否必选	说明
table_name	是	待执行`DELETE`操作的Transactional或Delta Table表名称。
alias	否	表别名。
where_condition	否	WHERE子句，用于筛选满足条件的数据。更多WHERE子句信息，请参见WHERE子句（WHERE_condition）。如果不带WHERE子句，会删除表中的所有数据。

使用示例

示例1：创建非分区表acid_delete，并导入数据，执行DELETE操作删除满足指定条件的行数据。命令示例如下：

--创建Transactional表acid_delete。
CREATE TABLE IF NOT EXISTS acid_delete (id BIGINT) TBLPROPERTIES ("transactional"="true");

--插入数据。
INSERT OVERWRITE TABLE acid_delete VALUES (1), (2), (3), (2);

--查看插入结果。
SELECT * FROM acid_delete;

+------------+
| id         |
+------------+
| 1          |
| 2          |
| 3          |
| 2          |
+------------+

-- 删除id为2的行，如果在MaxCompute客户端（odpscmd）执行，需要输入yes|no确认。
DELETE FROM acid_delete WHERE id = 2;
-- 等价于上面写法
DELETE FROM acid_delete ad WHERE ad.id = 2;

--查看结果表中数据只有1、3。
SELECT * FROM acid_delete;

+------------+
| id         |
+------------+
| 1          |
| 3          |
+------------+

示例2：创建分区表acid_delete_pt，并导入数据，执行DELETE操作删除满足指定条件的行。命令示例如下：

--创建Transactional表acid_delete_pt。  
CREATE TABLE IF NOT EXISTS acid_delete_pt (id BIGINT) PARTITIONED BY (ds STRING) TBLPROPERTIES ("transactional"="true");

--添加分区。
ALTER TABLE acid_delete_pt ADD IF NOT EXISTS PARTITION (ds = '2019');
ALTER TABLE acid_delete_pt ADD IF NOT EXISTS PARTITION (ds = '2018');

--插入数据。
INSERT OVERWRITE TABLE acid_delete_pt PARTITION (ds = '2019') VALUES (1), (2), (3);
INSERT OVERWRITE TABLE acid_delete_pt PARTITION (ds = '2018') VALUES (1), (2), (3);

--查看插入结果。
SELECT * FROM acid_delete_pt;

+------------+------------+
| id         | ds         |
+------------+------------+
| 1          | 2018       |
| 2          | 2018       |
| 3          | 2018       |
| 1          | 2019       |
| 2          | 2019       |
| 3          | 2019       |
+------------+------------+

--删除分区为2019且id为2的数据，如果在MaxCompute客户端（odpscmd）执行，需要输入yes|no确认。
DELETE FROM acid_delete_pt WHERE ds = '2019' AND id = 2;

--查看结果表中已删除分区为2019且id为2的数据。
SELECT * FROM acid_delete_pt;

+------------+------------+
| id         | ds         |
+------------+------------+
| 1          | 2018       |
| 2          | 2018       |
| 3          | 2018       |
| 1          | 2019       |
| 3          | 2019       |
+------------+------------+

示例3：创建目标表acid_delete_t和关联表acid_delete_s，通过关联操作删除满足指定条件的行。命令示例如下：

--创建目标Transactional表acid_delete_t和关联表acid_delete_s。 
CREATE TABLE IF NOT EXISTS acid_delete_t (id INT, value1 INT, value2 INT) TBLPROPERTIES ("transactional"="true");
CREATE TABLE IF NOT EXISTS acid_delete_s (id INT, value1 INT, value2 INT);

--插入数据。
INSERT OVERWRITE TABLE acid_delete_t VALUES (2, 20, 21), (3, 30, 31), (4, 40, 41);
INSERT OVERWRITE TABLE acid_delete_s VALUES (1, 100, 101), (2, 200, 201), (3, 300, 301);

--删除acid_delete_t表中id与acid_delete_s表中id不匹配的行。如果在MaxCompute客户端（odpscmd）执行，需要输入yes|no确认。
DELETE FROM acid_delete_t WHERE NOT EXISTS (SELECT * FROM acid_delete_s WHERE acid_delete_t.id = acid_delete_s.id);
--等价上面写法。
DELETE FROM acid_delete_t a WHERE NOT EXISTS (SELECT * FROM acid_delete_s b WHERE a.id = b.id);

--查看结果表中只有id为2、3的数据。
SELECT * FROM acid_delete_t;

+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 20         | 21         |
| 3          | 30         | 31         |
+------------+------------+------------+

示例4：创建Delta Table表mf_dt，并导入数据，执行DELETE操作删除满足指定条件的行。命令示例如下：

--创建目标Delta Table表mf_dt。 
CREATE TABLE IF  NOT EXISTS mf_dt (pk BIGINT  NOT NULL PRIMARY KEY, 
                  val  BIGINT NOT NULL) 
                  PARTITIONED BY(dd STRING, hh STRING) 
                  tblproperties ("transactional"="true");

--插入数据
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='02') VALUES (1, 1), (2, 2), (3, 3);

--查看插入结果                 
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';

--返回结果
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+

--删除分区为01和02，且val为2的数据。
DELETE FROM mf_dt WHERE val = 2  AND dd='01' AND hh='02';

--查看结果表中只有val为1、3的数据
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';

--返回结果
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
+------------+------------+----+----+