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