Can't figure out how to update a column in one table based on multiple boolean values in another table in postgresql

Question

What I want to happen is boolean milestone.milestonecompleted will turn true only if ALL of the linked boolean task.taskcompleted values are True I've looked around and come up with this. Any help would be great :)

UPDATE milestone
SET milestonecompleted = 
    CASE task.taskcompleted WHEN false then false ELSE true END --CASE 
FROM task, milestonedependency
WHERE milestone.milestoneid = milestonedependency.milestoneid 
      AND task.taskid = milestonedependency.taskid 
      AND milestone.milestoneid = 1  

Answer 1

If a milestone can have multiple tasks (which is highly likely), then your statement will only update the taskcompleted for the "last" task processed in the query (the "last" is undefined here though).

If you want to check all tasks for a milestone to be completed you need consider all taskcompleted combined with an AND condition. This can be done using the boolean aggregate function bool_and().

So get evaluate the "all completed", you would need something like this:

select md.milestoneid, 
       bool_and(t.taskcompleted) as all_completed
FROM task t
  JOIN milestonedependency md ON t.taskid = md.taskid 
GROUP by m.milestoneid 

This will return a flag for each milestoneid if all tasks have been completed. null values will be ignored (as with all aggregate functions).

To use this in an update statement, you can use something like this:

UPDATE milestone
   SET milestonecompleted = tmp.all_completed
FROM ( 
  select md.milestoneid, 
         bool_and(t.taskcompleted) as all_completed
  FROM task t
    JOIN milestonedependency md ON t.taskid = md.taskid 
  GROUP by md.milestoneid 
) tmp
WHERE tmp.milestoneid = milestone.milestoneid 
  AND milestone.milestoneid = 1;

You can make this more efficient by pushing the condition on the milestoneid into the derived table so that only rows with that milestoneid are aggregated:

UPDATE milestone
   SET milestonecompleted = tmp.all_completed
FROM ( 
  select md.milestoneid, 
         bool_and(t.taskcompleted) as all_completed
  FROM task t
    JOIN milestonedependency md ON t.taskid = md.taskid 
  GROUP by md.milestoneid 
  WHERE md.milestoneid = 1
) tmp
WHERE tmp.milestoneid = milestone.milestoneid;