DELETE

语法

DELETE FROM table_name [ WHERE condition ]

描述

从表中删除数据行。

当前版本，使用delete可以删除整个表的数据，或者分区表的指定分区。

对于事务表（指定了属性transactional = true），如果指定了where条件，将删除条件匹配的数据行。

示例

非事务表场景：

• 清空表数据

  --创建表并插入数据
   create table tb_del as select * from (values(1,'suse'),(2,'centos'),(3,'euler')) as t (id,os);
  select * from tb_del;
   id |   os   
  ----|--------
    1 | suse   
    2 | centos 
    3 | euler  
  (3 rows)
  
  --不支持通过where子句删除单条数据
  delete from tb_del where id =1;
  Query 20201116_081955_00027_iyct5@default@HetuEngine failed: This connector only supports delete where one or more partitions are deleted entirely for Non-Transactional tables
  
  --清空表数据
  delete from tb_del;
  select * from tb_del;
   id | os 
  ----|----
  (0 rows)

• 删除分区表hive.web.page_views中partition(date='2020-07-17', country='US')的分区：

  delete from hive.web.page_views where ds=date '2020-07-17' and country='US';

--创建事务表     
create table tb_trans(a int,b string) with (transactional=true);
CREATE TABLE

--插入数据
insert into tb_trans values(1,'a'),(2,'b'),(3,'c');
INSERT: 3 rows

--删除数据
delete from tb_trans where a=1;
DELETE: 1 row