您可能想尝试以下方法:
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 );
测试用例:
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);
结果:
SELECT * FROM Members;
+----------+--------------+
| MemberId | PointsEarned |
+----------+--------------+
| 1 | 23 |
| 2 | 10 |
| 3 | 3 |
+----------+--------------+
3 rows in set (0.00 sec)