CODEWITHSUNDEEP
mysql
Don Rhummy
Don Rhummy

Reputation: 25830

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)

Upvotes: 0

Views: 32

Answers (2)

juergen d
juergen d

Reputation: 204766

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

Upvotes: 2

Lance Bryant
Lance Bryant

Reputation: 157

Short answer is:

UPDATE State LEFT JOIN Actions ON Actions.id = State.id SET duration = FLOOR(AVG(duration)) WHERE Actions.id IN ( select id from Actions where Actions.id = state.id and type = 'started' GROUP BY Actions.id )

Probably not quite syntactically correct.

Upvotes: 0

Related Questions