MySQL: Update table column from subquery result

Question

On the Members table are columns "MemberID" and "PointsEarned".

I want to update the PointsEarned column from the result of this query:

SELECT m.MemberID, m.UserName, 
     ( (SELECT COUNT(*) FROM EventsLog as e WHERE e.MemberID=m.MemberID AND e.EventsTypeID=2)*10 ) + 
     ( (SELECT COUNT(*) FROM EventsLog as e WHERE e.MemberID=m.MemberID AND e.EventsTypeID=3)*3 ) +
     ( (SELECT COUNT(*) FROM ChatMessages as c WHERE c.MemberID=m.MemberID)*.1 )
     as PointsEarned
   FROM Members as m

Can anybody tell me how I should do it with a single query?

Thanks!

Answer 1

You may want to try the following:

UPDATE   Members m
SET      PointsEarned = 
     ( (SELECT COUNT(*) FROM EventsLog as e WHERE e.MemberID=m.MemberID AND e.EventsTypeID=2)*10 ) + 
     ( (SELECT COUNT(*) FROM EventsLog as e WHERE e.MemberID=m.MemberID AND e.EventsTypeID=3)*3 ) +
     ( (SELECT COUNT(*) FROM ChatMessages as c WHERE c.MemberID=m.MemberID)*.1 );

Test case:

CREATE TABLE Members (MemberId int, PointsEarned int);
CREATE TABLE EventsLog (MemberID int, EventsTypeID int);
CREATE TABLE ChatMessages (MemberID int);

INSERT INTO Members VALUES (1, 0);
INSERT INTO Members VALUES (2, 0);
INSERT INTO Members VALUES (3, 0);

INSERT INTO EventsLog VALUES (1, 2);
INSERT INTO EventsLog VALUES (1, 2);
INSERT INTO EventsLog VALUES (1, 3);
INSERT INTO EventsLog VALUES (2, 2);
INSERT INTO EventsLog VALUES (3, 3);

INSERT INTO ChatMessages VALUES (1);
INSERT INTO ChatMessages VALUES (1);
INSERT INTO ChatMessages VALUES (2);

Result:

SELECT * FROM Members;
+----------+--------------+
| MemberId | PointsEarned |
+----------+--------------+
|        1 |           23 |
|        2 |           10 |
|        3 |            3 |
+----------+--------------+
3 rows in set (0.00 sec)