clickhouse_mutations_with_row_condition.sql

create table delete_test Engine=MergeTree() ORDER BY id AS SELECT number as id from numbers(1000000);

SELECT count() FROM delete_test;
SELECT * FROM delete_test where id<10;

/*  mutations are executed asyncroniously, so you may need to wait a bit... */ 

alter table delete_test delete where id in (1,2,3,5,7);

SELECT count() FROM delete_test;
SELECT * FROM delete_test where id<10;

/*
if you will have too many elements in set (like thousands) - you will probabaly hit some of query complexity limitations. 
you can relax some of limitations if you need extend it just a bit, or (better) fill the table with ids to be deleted . 
in replicated/sharded case you need to create such a table on all nodes (it can be also MergeTree / ReplicatedMergeTree) 
*/

create table delete_ids Engine=Log AS SELECT number from numbers(1000000) where number % 1000 != 0;

alter table delete_test delete where id in (delete_ids);

/*  mutations are executed asyncroniously, so you may need to wait a bit... */ 

SELECT count() FROM delete_test;
SELECT * FROM  delete_test; 


/*

With multiple columns it works exactly the same:

*/


create table delete_test Engine=MergeTree() ORDER BY ( id, str ) AS SELECT number as id, toString( rand() % 10) as str  from numbers(1000000);

alter table delete_test delete where (id, str) in ( (1,'1'), (2,'2') );
-- OR if you need just to check both columns for subset
alter table delete_test delete where id in (...) and str in (...);
 
create table delete_ids Engine=Log AS SELECT id, str FROM delete_test LIMIT 900000; 
alter table delete_test delete where (id, str) in delete_ids;
SELECT count() FROM delete_test;