bizard bizard - 7 months ago 9
SQL Question

update one line with the average of other line



I actually have a table like this :

enter image description here

But I want to update the value of the first line to give it the value of the average of their child like this :

enter image description here

So I've used this query :


UPDATE mytable parent
INNER JOIN mytable child
ON child.parentId = parent.id
SET parent.value = AVG(child.value)
WHERE parent.parentId=0


But I get this error : #1111 - Invalid use of group function

How can I correct my query ?

Answer

Use a subquery to join on where you calculate the average for the parent id's

The query to get the average per parentId looks like:

SELECT parentId, AVG(value) as average FROM mytable GROUP BY parentId

So now join on it

UPDATE mytable parent
INNER JOIN (SELECT parentId, AVG(value) as average FROM mytable GROUP BY parentId) child
ON child.parentId = parent.id
SET parent.value = child.average
WHERE parent.parentId=0
Comments