DELETE

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