CODEWITHSUNDEEP
mysqlsql
vienya
vienya

Reputation: 57

SQL - Update if all bool values for an id are true

I have three tables:

task: (t_id is a primary key, all_done is boolean)

+------+-------------+
| t_id |    all_done |
+------+-------------+
|    1 |           0 |
|    2 |           0 |
+------+-------------+

subtask: (s_id is a primary key, done is boolean)

+------+------------+
| s_id |       done |
+------+------------+
|    1 |          0 |
|    2 |          0 |
|    3 |          1 |
|    4 |          1 |
|    5 |          1 |
|    6 |          0 |
|    7 |          0 |
+------+------------+

task_subtask: (s_id and t_id are foreign keys. not every subtask belongs to a task)

+------+------+
| t_id | s_id |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
|    2 |    4 |
|    2 |    5 |
+------+------+

The table task_subtask shows which subtask belongs to which task.
My intention is to make an sql-call to set all_done = 1 if all subtasks of that task are done. So in this example all_done should be set to 1 for t_id = 2

The following code seems to work, but i can't manage to fill this into an UPDATE call and i'm not really sure if i'm thinking into the right direction:

SELECT x.t_id, count(x.t_id), SUM(x.done) FROM (
    SELECT subtask.s_id, subtask.done, task_subtask.t_id FROM subtask JOIN task_subtask WHERE subtask.s_id = task_subtask.s_id
) AS x GROUP BY x.t_id;

With that statement i'd get this output:

+------+---------------+-------------+
| t_id | count(x.t_id) | SUM(x.done) |
+------+---------------+-------------+
|    1 |             3 |           1 |
|    2 |             2 |           2 |
+------+---------------+-------------+

My best try might have been this:

UPDATE task, (SELECT x.t_id, count(x.t_id), SUM(x.done) FROM (
        SELECT subtask.s_id, subtask.done, task_subtask.t_id FROM subtask JOIN task_subtask WHERE subtask.s_id = task_subtask.s_id
    ) AS x GROUP BY x.t_id)
SET task.all_done = 1 WHERE task.t_id = t_id AND count(x.t_id) = SUM(x.done);

The error message with this call is

ERROR 1248 (42000): Every derived table must have its own alias

but i'm not sure if the update call would even work this way.

Thanks in advance for any help!

Upvotes: 1

Views: 53

Answers (2)

user6695319
user6695319

Reputation:

If you just merge task_subtask as the same table, adding T_ID field,

task_table: 

+------+-------------+
| t_id |    all_done |
+------+-------------+
|    1 |           0 |
|    2 |           0 |
+------+-------------+

subtask_table: (s_id,t_id is a primary key)

+------+------+------------+
| s_id | t_id |       done |
+------+-------------------+
|    1 |    1 |          0 |
|    2 |    1 |          0 |
|    3 |    1 |          1 |
|    4 |    2 |          1 |
|    5 |    2 |          1 |
 etc...
+------+------|------------+

Now you can try 

UPDATE TASK SET all_done=1 WHERE 
count
    (Select done from subtask join task 
    on subtask.t_id=task.t_id WHERE done=0)=0

That means, for each Task, if don't have more subtask where done=0, then update Task set all_done=1

Another good way: Create a trigger AFTER update subTask, with the same logic above.

CREATE TRIGGER set_all_done
AFTER UPDATE
   ON subtask FOR EACH ROW

BEGIN    
   IF count(Select done from subtask  WHERE subtask.t_id = new.t_id)=0
then UPDATE TASK SET all_done=1 WHERE Task.t_id = new.t_id

END;

I look now and have a third solution, using just one table:

SELECT distinct t_id from subtask where done=0

This single line returns t_id not yet done, and you can use it as verification, or 

UPDATE TASK SET all_done=1 WHERE t_id NOT in (SELECT distinct t_id from subtask where done=0)

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You were complicating the query. I think this should work.

UPDATE task 
JOIN (SELECT ts.t_id 
      FROM subtask s
      JOIN task_subtask ts ON s.s_id = ts.s_id
      GROUP BY ts.t_id
      HAVING COUNT(s.s_id) = SUM(s.done) 
      ) x
ON task.t_id = x.t_id
SET all_done = 1

Upvotes: 1

Related Questions