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;