The DELETE statement is used to delete rows from the target table based on specified conditions.

Currently, only unique key tables support the use of the DELETE statement (DELETE). For other types of tables, please use the "ALTER TABLE - DELETE" statement. For details, see Delete operation (DELETE)

DELETE FROM [db.]table WHERE expr;

-- The engine by default ensures the uniqueness of the unique key in the partition 

CREATE TABLE  test.unique_table 
( 
  `event_time` DateTime, 
  `product_id` UInt64, 
  `city` String, 
  `category` String, 
  `amount` UInt32, 
  `revenue` UInt64 
) 
ENGINE = CnchMergeTree 
PARTITION BY toDate(event_time) 
ORDER BY (city, category) 
UNIQUE KEY product_id; 

INSERT INTO  test.unique_table VALUES 
('2020-10-29 23:40:00', 10001, 'Beijing', 'men's clothing', 5, 500), 
('2020-10-29 23:40:00', 10002, 'Beijing', 'men's clothing', 2, 200), 
('2020-10-29 23:40:00', 10003, 'Beijing', 'men's clothing', 1, 100); 

-- Use DELETE FROM to delete data where revenue == 500 
DELETE FROM  test.unique_table WHERE revenue = 500; 
-- Use DELETE FROM to delete data where revenue >= 200 
DELETE FROM  test.unique_table WHERE revenue >= 200; 

-- In the query results, data with revenue >= 200 has been deleted 
select * from  test.unique_table order by toDate(event_time), product_id; 
┌──────────event_time─┬─product_id─┬─city────┬─category─┬─amount─┬─revenue─┐ 
│ 2020-10-29 23:40:00 │      10003 │ Beijing │ men's clothing │      1 │     100 │ 
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘