CODEWITHSUNDEEP
mysqlsqlsql-updateconditional-statements
coffeemonitor
coffeemonitor

Reputation: 13120

MySQL - Subquery Condition

I have 2 tables:

tbl_projects tbl_projects_tasks

It's a simple tasks management interface. And the Tasks are assigned to Projects. I also have a column, inside the Tasks Table, called percentage, which is an integer field. I do keep it from 0 - 100, naturally for percentage reasons.

Also, inside the Projects table I have another column called: projectpercentagedone

This column Averages all Tasks related to an Project.

Here's the query that does the averaging (using a single projectid in my example):

UPDATE `tbl_projects` SET
`totaltasks` = (SELECT COUNT(taskid) AS T1 FROM `tbl_projects_tasks` WHERE projectid = 10),
`projectpercentagedone` = (SELECT AVG(percentage) AS T2 FROM `tbl_projects_tasks` WHERE projectid = 10) 
WHERE projectid = 10 
LIMIT 1

I want to go a step further, for more advanced settings reasons.

If the total AVG is 100%, I'd like to update the projectvisible of tbl_projects from 1 to 0.

Of course, I could do 2 queries in secession. But I'm sure a single query is way more cool. This type of conditional statement is a little above my experience. I'm looking for a little help in it.

Upvotes: 1

Views: 456

Answers (1)

John Woo
John Woo

Reputation: 263693

You can simply join tbl_projects into a subquery which do some calculations on tbl_projects_tasks. 

UPDATE  tbl_projects a
        INNER JOIN
        (
            SELECT  projectid, 
                    COUNT(taskID) totalCount,
                    AVG(percentage) avgPercent
            FROM    tbl_projects_tasks
            GROUP   BY projectid
        ) b ON  a.projectid = b.projectid
SET     a.totaltasks = b.totalCount,
        a.projectpercentagedone = b.avgPercent,
        a.projectvisible = IF(b.avgPercent = 100, 0, 1)
WHERE   a.projectid = 10

Upvotes: 1

Related Questions