[mysql] mysql update query with sub query

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

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

You can check your eav_attributes table to find the relevant attribute IDs for each image role, such as;

Then you can use those to set whichever role to any other role for all products like so;

UPDATE catalog_product_entity_varchar AS `v` INNER JOIN (SELECT `value`,`entity_id` FROM `catalog_product_entity_varchar` WHERE `attribute_id`=86) AS `j` ON `j`.`entity_id`=`v`.entity_id SET `v`.`value`=j.`value` WHERE `v`.attribute_id = 85 AND `v`.`entity_id`=`j`.`entity_id`

The above will set all your 'base' roles to the 'small' image of the same product.

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.

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.

Since SELECT id, COUNT(*) GROUP BY id will only count for existing values of ids,

whereas a SELECT COUNT(*) will always return a value, being 0 if no records are selected.

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

0-aware version of mellamokb query would be :

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.