Re: Updating a Parent Record with Values from Multiple Child Record Values

[Peter Brawley]

The spec looks incomplete, eg you say there are 5 coupon status values but you list just four, you don't show the column names ('key' is a mysql reserved word), and your criteria leave several logical possibilities undefined, so the following query will need tweaking but should something like this ...

update parent p
join (
  select 
    id,
    sum(if(status='void',1,0)) as void,
    sum(if(status='used',1,0)) as used,
    sum(if(status='susp',1,0)) as susp,  
    sum(if(status='open',1,0)) as open,
    count(*) as n
  from coupons
  where id=201
  group by id
) c using (id)
set p.status=if(c.susp>0,'susp',
                if(c.void>0,'void',
                   if(c.used>0 and c.open>0,'partially used',
                      if(c.open=c.n,'open', '????')
                     )
                  )
               );