MySQL更新查询与子查询

本文介绍了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

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