Don Rhummy Don Rhummy - 4 months ago 7
MySQL Question

How do I update with a join and group by?

I want to do a query like this:

UPDATE State
LEFT JOIN Actions ON Actions.id = State.id
SET duration = FLOOR(AVG(duration)) WHERE type = 'started' GROUP BY Actions.id


How would I do this? (It says the group by is an error)

Answer
UPDATE State
INNER JOIN 
(
   select id, FLOOR(AVG(duration)) as avg
   from Actions
   WHERE type = 'started'
   group by id        
) tmp ON tmp.id = State.id
SET duration = tmp.avg