Thanks, I didn't have the idea of an UPDATE with INNER JOIN.
In the original query, the mistake was to name the subquery, which must return a value and can't therefore be aliased.
UPDATE Competition
SET Competition.NumberOfTeams =
(SELECT count(*) -- no column alias
FROM PicksPoints
WHERE UserCompetitionID is not NULL
-- put the join condition INSIDE the subquery :
AND CompetitionID = Competition.CompetitionID
group by CompetitionID
) -- no table alias
should do the trick for every record of Competition.
To be noticed :
The effect is NOT EXACTLY the same as the query proposed by mellamokb, which won't update Competition records with no corresponding PickPoints.
Since SELECT id, COUNT(*) GROUP BY id will only count for existing values of ids,
whereas a SELECT COUNT(*) will always return a value, being 0 if no records are selected.
This may, or may not, be a problem for you.
0-aware version of mellamokb query would be :
Update Competition as C
LEFT join (
select CompetitionId, count(*) as NumberOfTeams
from PicksPoints as p
where UserCompetitionID is not NULL
group by CompetitionID
) as A on C.CompetitionID = A.CompetitionID
set C.NumberOfTeams = IFNULL(A.NumberOfTeams, 0)
In other words, if no corresponding PickPoints are found, set Competition.NumberOfTeams to zero.