SQL update query with join and subquery
This seems to work:

Update companies c
inner join (select a.id as company_id, sum(case when b.hot_tip = 1 then ((@maxrank-c.rank)/(@maxrank-1)) * (b.score-3) else 0 end) as cc_score
from companies a
left join table2 b
on a.id = b.company_id
left join table3 c
on b.table3_id = c.id
group by a.id) x ON x.company_id = c.id
set c.c_c_score = x.cc_score;