MySQL更新查询与子查询

  • Z时代
  • 2024-01-10
  • 分类：问答

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

当我运行它时，我得到：

＃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

回答：

主要问题是内部查询不能与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

以上是 MySQL更新查询与子查询 的全部内容， 来源链接： utcz.com/qa/424841.html

回到顶部