使用子查询的mysql更新查询

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服务器版本对应的手册，找到正确的语法，以便在“a where a”附近使用。CompetitionID =竞争。CompetitionID”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

3 个解决方案

#1

117

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.

主要问题是，内部查询不能与外部更新语句中的where子句相关，因为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

Demo: http://www.sqlfiddle.com/#!2/a74f3/1

演示:http://www.sqlfiddle.com/ ! 2 / a74f3/1

#2

Thanks, I didn't have the idea of an UPDATE with 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提出的查询并不完全相同，它不会在没有相应的选取点的情况下更新竞争记录。

Since SELECT id, COUNT(*) GROUP BY id will 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.

而SELECT COUNT(*)总是返回一个值，如果没有选择记录，则为0。

This may, or may not, be a problem for you.

这对你来说可能是个问题，也可能不是。

0-aware version of mellamokb query would be :

了解mellamokb查询的版本为:

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.

换句话说，如果没有找到相应的选取点，就设置竞争。NumberOfTeams为零。

#3

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 = target.id
SET t.count = aggregate.count

That's @mellamokb's answer, as above, reduced to the max.

这是@mellamokb的答案，如上所述，被简化为最大值。

#1

117

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