Try wrapping the CASE/conditional statement inside a MySQL trigger

Question

I have a trigger:

CREATE TRIGGER Moves 
AFTER INSERT ON Rolls
FOR EACH ROW
UPDATE Players
CASE
    WHEN P_Location + NEW.Rolls < 17 THEN
    SET P_Location = P_Location + NEW.Rolls
    WHERE id = NEW.Player_id
    ELSE 
    SET P_Location = NEW.Rolls - (16 - P_Location)
END;

But the syntax is incorrect in some places and generates errors. I've checked for similar questions on this site, but the questions I looked at seemed to use methods that didn't seem to work in this case (specifically using IF ). I want the main condition to be

IF P_Location + NEW.Rolls < 17 THEN
SET P_Location = P_Location + NEW.Rolls
WHERE id = NEW.Player_id

while the ELSE part is

SET P_Location = NEW.Rolls - (16 - P_Location)

Inside the trigger. Can anyone explain how I can do this?

Answer 1

The CASE expression must evaluate to a scalar, but you are concatenating other clauses through the CASE expression.

You can do this so the result of CASE returns a scalar and assign that scalar to the P_Location column.

UPDATE Players
SET P_Location = CASE WHEN P_Location + NEW.Rolls < 17
                 THEN P_Location + NEW.Rolls
                 ELSE NEW.Rolls - (16 - P_Location)
                 END
WHERE id = NEW.Player_id;