SQLTeam.com | Weblogs | Forums

Need help with update-query, image provided


I want to update all rows in tblAAA that satisfy a condition.

A tblAAA has 1 or more tblBBB.
A tblBBB has 1 tblCCC.
I want to update all rows in tblAAA where all of its tblBBB has a tblCCC where tblCCC.status = 1.

Please see provided image.

Really greatful for help, I have been staring at this for two hours but have no clue.


Things would be clearer if you'd give us the table definitions instead of the image. Is this one UPDATE statement or two? Your requirements lists a "status" field in the tblCCC table but not in the image. Can you clarify?

SET ?= ?
    SELECT tblBBB.AAA_id
    FROM tblBBB
    INNER JOIN tblCCC ON tblCCC.CCC_id = tblBBB.CCC_id
    GROUP BY tblBBB.AAA_id
    HAVING COUNT(*) = SUM(CASE WhEN tblCCC.active = 1 THEN 1 ELSE 0 END)
) AS matches ON matches.AAA_id = a.AAA_id