MySQL更新查询与子查询

一尘不染

MySQL更新查询与子查询

mysql

谁能看到以下查询出了什么问题？

当我运行它时，我得到：

＃1064-您的SQL语法有误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在第8行的’a where a.CompetitionID
= Competition.CompetitionID’附近使用

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

阅读 628

2020-05-17

共1个答案

一尘不染

主要问题是内部查询不能与where外部update语句上的子句相关，因为在内部子查询执行之前，where过滤器首先应用于要更新的表。处理这种情况的典型方法是多表更新。

Update
  Competition as C
  inner 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 = A.NumberOfTeams

演示：http ://www.sqlfiddle.com/#!2/
a74f3/1

2020-05-17