IF Sentance in MySQL trigger-CodePudding

I am building an automatic ranking on a game server and i have to do this by manipulating the MySQL database, this is not a strong knowledge area for me so ive tried to be a bit hacky with this. i need to use the trigger function of SQL db.

CREATE TRIGGER `ranking_up` AFTER UPDATE ON `highscore` FOR EACH ROW

BEGIN

IF NEW.score >= '10' and <= '100' THEN
    UPDATE department_members
    SET rankID=1 WHERE userID = NEW.userID;
END IF;
IF NEW.score >= '100' and <= '300' THEN
    UPDATE department_members
    SET rankID=2 WHERE userID = NEW.userID;
END IF;
IF NEW.score >= '300' THEN
    UPDATE department_members
    SET rankID=3 WHERE userID = NEW.userID;
END IF;
END

I get the standard MySQL #1064 and i have tried to talk to my rubber duck... does not work it makes perfect sense for me but apparently does not work.

I am looking for the answer ofc BUT i also want to learn on my mistake here, what am i doing wrong ?

CodePudding user response：

You can't get away with NEW.score >= '10' and >= '100' you need to repeat new.score NEW.score >= '10' and new.score >= '100' . More digestibly you could use between if new.score is stored as an integer...

delimiter $$
CREATE TRIGGER `ranking_up` AFTER UPDATE ON `highscore` FOR EACH ROW

BEGIN

IF NEW.score between 10 and  99 THEN
    UPDATE department_members
    SET rankID=1 WHERE userID = NEW.userID;
END IF;
IF NEW.score between 100 and 299 THEN
    UPDATE department_members
    SET rankID=2 WHERE userID = NEW.userID;
END IF;
IF NEW.score >= 300 THEN
    UPDATE department_members
    SET rankID=3 WHERE userID = NEW.userID;
END IF;
END $$
delimiter ;

see https://www.db-fiddle.com/f/51PoUTCMAuWFoYaAz5ubW7/0

OR you could dispense with the ifs entirely and use case within the update statement.

delimiter $$
CREATE TRIGGER `ranking_up` AFTER UPDATE ON `highscore` FOR EACH ROW

BEGIN


    UPDATE department_members
    SET RANK = 
     CASE 
        WHEN NEW.score between 10 and  99  THEN 1
        WHEN NEW.score between 100 and 299 THEN 2
        WHEN NEW.score >= 300 THEN 3
     END
     WHERE userID = NEW.userID;
END $$
delimiter ;