Insert data to table if subquery exists

Question

What I want to do is check if a subquery exists, if exist then insert some data into a table, like:

if exists (select * from tableB)
 update tableA set status=1 where id = 1001;

I cannot update the ClickHouse table, so I created a table based on replacing merge tree in ClickHouse, and I tried SQL like the one below in ClickHouse.

insert into TableA select id, 1 as status, '2023-08-27 00:00:00' from TableA where id=1001 where exists (select * from tableB)

Obviously, this doesn't seem right. How do I implement this in ClickHouse?

Answer 1

What you describe can be achieved without a ReplacingMergeTree:

create table tab1 (a String) engine=MergeTree order by tuple();
create table tab2 (a String) engine=MergeTree order by tuple();

insert into tab1 values ('original value');
alter table tab1 update a = 'changed value' where (select exists(select * from tab2));
select * from tab1; -- returns 'original value'

insert into tab2 values ('something');
alter table tab1 update a = 'changed value' where (select exists(select * from tab2));
select * from tab1; -- returns 'changed value'