Can anyone see what is wrong with the below query?
When I run it I get:
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
near 'a where a.CompetitionID = Competition.CompetitionID' at line 8
Update Competition
Set Competition.NumberOfTeams =
(
SELECT count(*) as NumberOfTeams
FROM PicksPoints
where UserCompetitionID is not NULL
group by CompetitionID
) a
where a.CompetitionID = Competition.CompetitionID
Best Answer
The main issue is that the inner query cannot be related to your
where
clause on the outerupdate
statement, because the where filter applies first to the table being updated before the inner subquery even executes. The typical way to handle a situation like this is a multi-table update.Demo: http://www.sqlfiddle.com/#!2/a74f3/1