Запрос обновления mysql с подзапросом

Может ли кто-нибудь увидеть, что не так с приведенным ниже запросом?

Когда я запускаю его, я получаю:

# 1064 - У вас есть ошибка в синтаксисе SQL; проверьте руководство, соответствующее версии сервера MySQL, для правильного использования синтаксиса около 'a, где a.CompetitionID = Competition.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

Ответ 1

Основная проблема заключается в том, что внутренний запрос не может быть связан с вашим предложением 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

Демо: http://www.sqlfiddle.com/#!2/a74f3/1

Ответ 2

Спасибо, у меня не было идеи UPDATE с INNER JOIN.

В исходном запросе ошибка заключалась в том, чтобы называть подзапрос, который должен возвращать значение и поэтому не может быть псевдонимом.

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

должен делать трюк для каждой записи Конкурса.

Следует заметить:

Эффект НЕ ТОЧНО так же, как запрос, предложенный mellamokb, который не будет обновлять записи о соревнованиях без соответствующих PickPoints.

Так как SELECT id, COUNT(*) GROUP BY id будет учитываться только для существующих значений ids,

тогда как a SELECT COUNT(*) всегда будет возвращать значение, равное 0, если не выбраны никакие записи.

Это может быть или не может быть проблемой для вас.

Версия запроса 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)

Другими словами, если не найдено соответствующих PickPoints, установите для параметра Competition.NumberOfTeams значение 0.

Ответ 3

Для нетерпеливых:

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

Этот ответ @mellamokb, как указано выше, сводится к макс.