create table maintable (
  id  integer not null primary key,
  id1 integer not null unique,
  id2 integer not null unique
);

create table child1(
  id  integer not null primary key,
  id1  integer not null,
  constraint x1 foreign key(id1) references maintable(id1)
);

create table child2(
  id  integer not null primary key,
  id2  integer not null,
  constraint x2 foreign key(id2) references maintable(id2)
);

insert into maintable values ( 1 , 1 , 1 ), ( 2 , 2 , 2 );
insert into child1 values( 1 , 1 ), ( 2 , 2 );
insert into child2 values( 1 , 1 ), ( 2 , 2 );

with
  tblmain as (
    select * 
    from old table 
      (
        delete from maintable 
        where id =  1 
      )
   ),
   tblclild1 as (
     select * 
     from old table 
       (
         delete from child1 
         where child1.id1 in
            (select id1 from tblmain)
       )
   ),
   tblclild2 as (
     select * 
     from old table 
       (
         delete from child2 
         where child2.id2 in
            (select id2 from tblmain)
       )
   )
select
   count(*),
   (select count(*) from tblclild1),
   (select count(*) from tblclild2)
from
   tblmain 
;
select * from maintable;
select * from child1;
select * from child2;

drop table maintable;
drop table child1;
drop table child2;

WITH  
  tblclild1(id,id1) as (
    select id,id1 
    from old table 
      (
        delete from child1 c1
        where c1.id1 in
           (select t.id1 from maintable t where t.id= 1 )
      ) 
  ),
  tblclild2(id,id2)as(
    select id,id2 
    from old table 
      (
        delete from child2 c2
        where c2.id2 in
           (select t.id2 from maintable t where t.id= 1 )
      ) 
  ),
  tblmain (id,id1,id2) as (
    select id,id1,id2 
    from old table 
      (
        delete from maintable t
        where t.id =  1 
      )
  )
select
  count(*),
  (select count(*) from tblmain)
from
  (values((select count(*) from tblclild1))) as x1,
  (values((select count(*) from tblclild2))) as x2
;

        delete from child1 c1
        where c1.id1 in           (select t.id1 from maintable t where t.id= 1 );
        delete from child2 c2
        where c2.id2 in           (select t.id2 from maintable t where t.id= 1 );
        delete from maintable t
        where t.id =  1 ;
;