Need help with update-query, image provided

Transact-SQL
1

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.

map.png1186×699 10.5 KB

2

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?

3 
UPDATE a
SET ?= ?
FROM tblAAA a
INNER JOIN (
    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