MySQL syntax for update with join

Question

I have 3 tables with the following structure:

Table User

id
name
points

Table Group

id
name
group_score

Table UsersGroups (association table)

UserId
GroupId

I'm trying to calculate the group_score - simply through adding all points of the users inside the group - for all groups.

i tried the following query

UPDATE `Group` SET group_score = (SELECT SUM(User.points) FROM User
                                    JOIN UsersGroups
                                    ON User.id = UsersGroups.UserId
                                    GROUP BY UsersGroups.GroupId)

which gives me OperationalError ('Subquery returns more than 1 row')

What am I doing wrong?

Thanks for the help!

possible duplicate of [mysql update join](http://stackoverflow.com/questions/15209414/mysql-update-join) — Jens, Sep 10 '14 at 10:51

score 0 · Accepted Answer · answered Sep 10 '14 at 10:53

UPDATE `Group` JOIN (SELECT GroupId, SUM(User.points) as sumuser FROM User
                                    JOIN UsersGroups
                                    ON User.id = UsersGroups.UserId
                                    GROUP BY UsersGroups.GroupId) x
ON `Group`.Id=x.GroupId
SET group_score =x.sumuser

score 0 · Answer 2 · answered Sep 10 '14 at 10:54

try this

UPDATE `Group` SET group_score = (SELECT UsersGroups.GroupId SUM(User.points) FROM User
                                    JOIN UsersGroups
                                    ON User.id = UsersGroups.UserId
                                    GROUP BY UsersGroups.GroupId)

MySQL syntax for update with join

2 Answers2