Updating users table from complex SQL query, users.id not recognised?

Question

So the other day, I asked this question about how to combine three complex queries and found a way to do it. Now I'm trying to use those queries to update a field in the users table and can't find a way to make it work. Here's the query:

update users set field_sum =(
    select sum(field_sum) from (
        select sum(field_one) as field_sum
          from t_a join t_b on (t_a.bid = t_b.id) where t_b.user_id=users.id 
        union all
        select sum(field_two) as field_sum
          from t_c join t_d on (t_c.did = t_d.id) where t_d.user_id=users.id 
        union all
        select sum(field_three) as field_sum
          from t_e where t_e.user_id=users.id
    ) as field_sumT
)

When I try to run it, I get the following error: ERROR 1054 (42S22): Unknown column 'users.id' in 'where clause'. When I try removing the .user_id=users.id bit from each where clause, it will run but ends up with the total sum of field_sum, not just the field_sum for that user. Is there any way to accomplish this?

Answer 1

Use:

    UPDATE USERS u
LEFT JOIN (SELECT t_b.user_id,
                  SUM(field_one) as field_sum 
             FROM t_a 
             JOIN t_b on t_a.bid = t_b.id
         GROUP BY t_b.user_id) a ON a.user_id = u.id
LEFT JOIN (SELECT t_d.user_id,
                  SUM(field_two) as field_sum 
             FROM t_c 
             JOIN t_d on t_c.did = t_d.id
         GROUP BY t_d.user_id) b ON b.user_id = u.id
LEFT JOIN (SELECT t_e.user_id,
                  SUM(field_three) as field_sum 
             from t_e 
         GROUP BY t_e.user_id) c ON c.user_id = u.id
      SET field_num = COALESCE(a.field_sum, 0) + COALESCE(b.field_sum, 0) + COALESCE(c.field_sum, 0)

Caveat

This will set any users with no records in the supporting rows to have a field_sum value of zero. Do you only want to update those with a record in at least one of those tables?