MySQL 带有子查询的mysql更新查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11588710/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql update query with sub query
提问by user1542043
Can anyone see what is wrong with the below query?
任何人都可以看到以下查询有什么问题吗？
When I run it I get:
当我运行它时，我得到：
#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
#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
回答by mellamokb
The main issue is that the inner query cannot be related to your whereclause on the outer updatestatement, 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.
主要问题是内部查询不能与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
回答by Pierre-Olivier Vares
Thanks, I didn't have the idea of an UPDATE with INNER JOIN.
谢谢，我没有使用 INNER JOIN 更新的想法。
In the original query, the mistake was to name the subquery, which must return a value and can't therefore be aliased.
在原始查询中，错误是命名子查询，它必须返回一个值，因此不能使用别名。
UPDATE Competition
SET Competition.NumberOfTeams =
(SELECT count(*) -- no column alias
FROM PicksPoints
WHERE UserCompetitionID is not NULL
-- put the join condition INSIDE the subquery :
AND CompetitionID = Competition.CompetitionID
group by CompetitionID
) -- no table alias
should do the trick for every record of Competition.
应该为比赛的每条记录做好准备。
To be noticed :
需要注意：
The effect is NOT EXACTLY the same as the query proposed by mellamokb, which won't update Competition records with no corresponding PickPoints.
效果与 mellamokb 提出的查询并不完全相同，后者不会更新没有相应 PickPoints 的竞赛记录。
Since SELECT id, COUNT(*) GROUP BY idwill only count for existing values of ids,
由于 SELECT id, COUNT(*) GROUP BY id只会计算 id 的现有值，
whereas a SELECT COUNT(*)will always return a value, being 0 if no records are selected.
而 aSELECT COUNT(*)将始终返回一个值，如果没有选择记录，则为 0。
This may, or may not, be a problem for you.
这对您来说可能是，也可能不是。
0-aware version of mellamokb query would be :
mellamokb 查询的 0 感知版本将是：
Update Competition as C
LEFT 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 = IFNULL(A.NumberOfTeams, 0)
In other words, if no corresponding PickPoints are found, set Competition.NumberOfTeams to zero.
换句话说，如果没有找到相应的 PickPoints，则将 Competition.NumberOfTeams 设置为零。
回答by sun
For the impatient:
对于不耐烦的人：
UPDATE target AS t
INNER JOIN (
SELECT s.id, COUNT(*) AS count
FROM source_grouped AS s
-- WHERE s.custom_condition IS (true)
GROUP BY s.id
) AS aggregate ON aggregate.id = t.id
SET t.count = aggregate.count
That's @mellamokb's answer, as above, reduced to the max.
这是@mellamokb的答案，如上所述，减少到最大值。
