DELETE
DELETE
Name
DELETE
Description
该语句用于按条件删除指定 table（base index）partition 中的数据。
该操作会同时删除和此 base index 相关的 rollup index 的数据。
Syntax
语法一：该语法只能指定过滤谓词
DELETE FROM table_name [table_alias] [PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
WHERE
column_name op { value | value_list } [ AND column_name op { value | value_list } ...];
语法二：该语法只能在 UNIQUE KEY 模型表上使用
[cte]
DELETE FROM table_name [table_alias]
[PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
[USING additional_tables]
WHERE condition
Required Parameters
- table_name: 指定需要删除数据的表
- column_name: 属于 table_name 的列
- op: 逻辑比较操作符，可选类型包括：=, >, <, >=, <=, !=, in, not in
- value | value_list: 做逻辑比较的值或值列表
- WHERE condition: 指定一个用于选择删除行的条件
Optional Parameters
- cte: 通用表达式。可以是 'WITH a AS SELECT * FROM tbl' 形式
- PARTITION partition_name | PARTITIONS (partition_name [, partition_name]): 指定执行删除数据的分区名，如果表不存在此分区，则报错
- table_alias: 表的别名
- USING additional_tables: 如果需要在 WHERE 语句中使用其他的表来帮助识别需要删除的行，则可以在 USING 中指定这些表或者查询。
Returned Results
Delete 命令是一个 SQL 命令，返回结果是同步的，分为以下几种：
执行成功
如果 Delete 顺利执行完成并可见，将返回下列结果，Query OK表示成功
Query OK, 0 rows affected (0.04 sec)
{'label':'delete_e7830c72-eb14-4cb9-bbb6-eebd4511d251', 'status':'VISIBLE', 'txnId':'4005'}
提交成功，但未可见
Doris 的事务提交分为两步：提交和发布版本，只有完成了发布版本步骤，结果才对用户是可见的。若已经提交成功了，那么就可以认为最终一定会发布成功，Doris 会尝试在提交完后等待发布一段时间，如果超时后即使发布版本还未完成也会优先返回给用户，提示用户提交已经完成。若如果 Delete 已经提交并执行，但是仍未发布版本和可见，将返回下列结果
Query OK, 0 rows affected (0.04 sec)
{'label':'delete_e7830c72-eb14-4cb9-bbb6-eebd4511d251', 'status':'COMMITTED', 'txnId':'4005', 'err':'delete job is committed but may be taking effect later' }
结果会同时返回一个 json 字符串：
-
affected rows：表示此次删除影响的行，由于 Doris 的删除目前是逻辑删除，因此对于这个值是恒为 0； -
label：自动生成的 label，是该导入作业的标识。每个导入作业，都有一个在单 Database 内部唯一的 Label； -
status：表示数据删除是否可见，如果可见则显示VISIBLE，如果不可见则显示COMMITTED； -
txnId：这个 Delete job 对应的事务 id； -
err：字段会显示一些本次删除的详细信息。
提交失败，事务取消
如果 Delete 语句没有提交成功，将会被 Doris 自动中止，返回下列结果
ERROR 1064 (HY000): errCode = 2, detailMessage = {错误原因}
比如说一个超时的删除，将会返回 timeout 时间和未完成的(tablet=replica)
ERROR 1064 (HY000): errCode = 2, detailMessage = failed to delete replicas from job: 4005, Unfinished replicas:10000=60000, 10001=60000, 10002=60000
Note
- 使用聚合类的表模型（AGGREGATE、UNIQUE）只能指定 key 列上的条件。
- 当选定的 key 列不存在于某个 rollup 中时，无法进行 delete。
- 语法一中，条件之间只能是“与”的关系。若希望达成“或”的关系，需要将条件分写在两个 DELETE 语句中。
- 语法一中，如果为分区表，需要指定分区，如果不指定，doris 会从条件中推断出分区。两种情况下，doris 无法从条件中推断出分区：1) 条件中不包含分区列；2) 分区列的 op 为 not in。当分区表未指定分区，或者无法从条件中推断分区的时候，需要设置会话变量 delete_without_partition 为 true，此时 delete 会应用到所有分区。
- 该语句可能会降低执行后一段时间内的查询效率。影响程度取决于语句中指定的删除条件的数量。指定的条件越多，影响越大。
Example
-
删除 my_table partition p1 中 k1 列值为 3 的数据行
DELETE FROM my_table PARTITION p1
WHERE k1 = 3; -
删除 my_table partition p1 中 k1 列值大于等于 3 且 k2 列值为 "abc" 的数据行
DELETE FROM my_table PARTITION p1
WHERE k1 >= 3 AND k2 = "abc"; -
删除 my_table partition p1, p2 中 k1 列值大于等于 3 且 k2 列值为 "abc" 的数据行
DELETE FROM my_table PARTITIONS (p1, p2)
WHERE k1 >= 3 AND k2 = "abc"; -
使用
t2和t3表连接的结果，删除t1中的数据，删除的表只支持 unique 模型-- 创建 t1, t2, t3 三张表
CREATE TABLE t1
(id INT, c1 BIGINT, c2 STRING, c3 DOUBLE, c4 DATE)
UNIQUE KEY (id)
DISTRIBUTED BY HASH (id)
PROPERTIES('replication_num'='1', "function_column.sequence_col" = "c4");
CREATE TABLE t2
(id INT, c1 BIGINT, c2 STRING, c3 DOUBLE, c4 DATE)
DISTRIBUTED BY HASH (id)
PROPERTIES('replication_num'='1');
CREATE TABLE t3
(id INT)
DISTRIBUTED BY HASH (id)
PROPERTIES('replication_num'='1');
-- 插入数据
INSERT INTO t1 VALUES
(1, 1, '1', 1.0, '2000-01-01'),
(2, 2, '2', 2.0, '2000-01-02'),
(3, 3, '3', 3.0, '2000-01-03');
INSERT INTO t2 VALUES
(1, 10, '10', 10.0, '2000-01-10'),
(2, 20, '20', 20.0, '2000-01-20'),
(3, 30, '30', 30.0, '2000-01-30'),
(4, 4, '4', 4.0, '2000-01-04'),
(5, 5, '5', 5.0, '2000-01-05');
INSERT INTO t3 VALUES
(1),
(4),
(5);
-- 删除 t1 中的数据
DELETE FROM t1
USING t2 INNER JOIN t3 ON t2.id = t3.id
WHERE t1.id = t2.id;预期结果为，删除了
t1表id为1的列+----+----+----+--------+------------+
| id | c1 | c2 | c3 | c4 |
+----+----+----+--------+------------+
| 2 | 2 | 2 | 2.0 | 2000-01-02 |
| 3 | 3 | 3 | 3.0 | 2000-01-03 |
+----+----+----+--------+------------+ -
使用 cte 关联删除
create table orders(
o_orderkey bigint,
o_totalprice decimal(15, 2)
) unique key(o_orderkey)
distributed by hash(o_orderkey) buckets 1
properties (
"replication_num" = "1"
);
insert into orders values
(1, 34.1),
(2, 432.8);
create table lineitem(
l_linenumber int,
o_orderkey bigint,
l_discount decimal(15, 2)
) unique key(l_linenumber)
distributed by hash(l_linenumber) buckets 1
properties (
"replication_num" = "1"
);
insert into lineitem values
(1, 1, 1.23),
(2, 1, 3.21),
(3, 2, 18.08),
(4, 2, 23.48);
with discount_orders as (
select * from orders
where o_totalprice > 100
)
delete from lineitem
using discount_orders
where lineitem.o_orderkey = discount_orders.o_orderkey;
Keywords
DELETE
