mysql查询结果更新到新表_MySQL：从子查询结果更新表列

您可能想尝试以下方法：

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)