mysql update query with sub query

Question

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

User · Accepted Answer

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

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] mysql update query with sub query

Examples related to mysql

Examples related to sql