0

I have:

#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;

I get an error in 'SET remainingProteinDairyPoints = (select familySize from user where userID = 1) * 3;' I'm trying to reset points every first day of the month. The points are based on the user's family size. How can I access userid's family size in SET remainingProteinDairyPoints?

stelity
  • 33
  • 8
  • What error are you getting? – Barmar May 01 '20 at 02:28
  • you can try to add DELIMITER $$ at the beginning and DELIMITER ; at the end – nbk May 01 '20 at 02:47
  • Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 8 which is where SET remainingProteinDairy Points is. I know it's not the right syntax – stelity May 01 '20 at 02:53
  • UPDATE: delimiter removed the error message. Thanks. But how can I do this dynamically and reset every user's points based on family size? – stelity May 01 '20 at 02:55

0 Answers0