Update parent based on child in SQL-CodePudding

I have a table in the below given format:

parent	child	exists
1234	2345	true
1234	3436	true
1234	4355	true
1234	0	false
1234	0	false

I would like to write a query which selects and updates the table if all non-zero child of a particular parent has exists = True. If that is the case, I would like to make all the exists for '0' child to 'true'

if even one value of a non-zero child is false, zero child should stay as 'false'.

Here is what I tried:

select item,child,
case when child = 0 and exists = 'False' 
and item in (SELECT item 
FROM table t1 
where not EXISTS
( SELECT t2.item 
    FROM table t2
    WHERE t2.item = t1.item and t2.child = t1.child  
    and t2.child <> 0
    and t2.exists = 'True'
  )) 
  then 'True' else exists end exists from (SELECT *
    FROM table 
)

CodePudding user response：

You just need a correlated subquery.

select * from dclee.test1 t1;

Update dclee.test1 t1
  set t1."EXISTS" = 'true'
    
    where t1.child = 0 
    and not exists (select 1 from dclee.test1 t2
                    where t1.parent = t2.parent 
                        and t2.child  0 
                        and t2."EXISTS" = 'false')
           and exists (select t3.parent, count(1) nz_cnt
                       from dclee.test1 t3
                       where t1.parent = t3.parent
                       and t3.child != 0
                       group by t3.parent
                       having count(1) > 0);
                        
commit;
            
drop table dclee.test1;

create table dclee.test1 as
select 1234 as Parent, 0 as child, 'false' as "EXISTS" from dual
union all
select 1234 as Parent, 2345 as child, 'true' as "EXISTS" from dual
union all 
select 1234 as Parent, 3436 as child, 'true' as "EXISTS" from dual
union all
select 1234 as Parent, 4355 as child, 'true' as "EXISTS" from dual
union all
select 1235 as Parent, 0 as child, 'false' as "EXISTS" from dual
union all
select 1235 as Parent, 5345 as child, 'true' as "EXISTS" from dual
union all 
select 1235 as Parent, 6436 as child, 'true' as "EXISTS" from dual
union all
select 1235 as Parent, 3436 as child, 'false' as "EXISTS" from dual
union all
select 1236 as Parent, 0 as child, 'false' as "EXISTS" from dual
union all
select 1236 as Parent, 0 as child, 'false' as "EXISTS" from dual