DELETE

Updated on 2022-11-18 GMT+08:00

View PDF

Syntax

DELETE FROM table_name [ WHERE condition ]

Description

This statement is used to delete data from a table.

In the current version, you can run the delete command to delete the data of an entire table or a specified partition in a partition table.

For a transaction table (the attribute transactional is set to true ), if the where condition is specified, the rows that match the condition are deleted.

Example

Non-transaction table scenario:

Clear the table data.

--Create a table and insert data into the table.
 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)

--A single data record cannot be deleted using the WHERE clause.
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

--Clear the table data.
delete from tb_del;
select * from tb_del;
 id | os 
----|----
(0 rows)

Delete the partition(date='2020-07-17', country='US') partition from the hive.web.page_views partition table.
```
delete from hive.web.page_views where ds=date '2020-07-17' and country='US';
```

Transaction table scenario: deleting a specified record

-- Create a transaction table.
create table tb_trans(a int,b string) with (transactional=true);
CREATE TABLE

-- Insert data.
insert into tb_trans values(1,'a'),(2,'b'),(3,'c');
INSERT: 3 rows

-- Delete data.
delete from tb_trans where a=1;
DELETE: 1 row

Parent topic: DML Syntax

Previous topic: INSERT

Next topic: UPDATE