带有子查询的mysql更新查询

2021-11-20 00:00:00 sql mysql

谁能看出下面的查询有什么问题?

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

相关文章