MySQL: update table column from subquery result

Question

In the Members table are the 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 someone tell me how I should do this with a single request?

Thanks!

Answer 1

You can 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)