mysql - MySQL：如何访问另一个表中的数据？

Question

我有：

#create a table to store user data
CREATE TABLE IF NOT EXISTS user(
userID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
lastName VARCHAR(32) NOT NULL,
firstName VARCHAR(32) NOT NULL,
gender VARCHAR(16) NOT NULL,
DOB VARCHAR(16) NOT NULL,
familySize INT,
);

#create a table to store user their remaining fruits/vegetable points
CREATE TABLE IF NOT EXISTS fruitsVegetablesPoints(
userID int,
remainingFruitsVegetablesPoints INT
);



#create a table to store user their remaining grain points
CREATE TABLE IF NOT EXISTS grainsPoints(
userID int,
remainingGrainsPoints INT
);


#create a table to store user their remaining protein points
CREATE TABLE IF NOT EXISTS proteinDairyPoints(
userID int,
remainingProteinDairyPoints INT
);

###############################################
#Need to know how to reset points every month
###############################################
CREATE EVENT resetPoints ON SCHEDULE 
       EVERY 1 MONTH
       STARTS '2020-04-30 19:58:00'
       ON COMPLETION PRESERVE ENABLE
    DO
    BEGIN
      UPDATE proteinDairyPoints
      SET remainingProteinDairyPoints = (select familySize from user where userID = 1)  * 3;
    END;

我在“SET remainingProteinDairyPoints = (select familySize from user where userID = 1) * 3;”中收到错误 我正在尝试在每个月的第一天重置积分。分数基于用户的家庭规模。如何在 SET remainingProteinDairyPoints 中访问用户 ID 的家庭规模？

标签： mysql