MySQL更新查询与子查询 [英] mysql update query with sub query

问题描述

有人可以看到以下查询出了什么问题吗?

Can anyone see what is wrong with the below query?

当我运行它时，我得到:

When I run it I get:

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

#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

推荐答案

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

The main issue is that the inner query cannot be related to your where clause on the outer update 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.

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更新查询与子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！