add a column to a table with 4 possible default value based on logical argument

Question

How can I add a column to a table with the number 1 to 4 based on each row meeting certain criteria?

Suppose I have a table: Like this with two columns (ID) and (RESULTS) and that I want to add a third column called (SCORE).

I want to give a score (between 1 and 4) to each row in my column based on whether the numbers in column (RESULTS) meet certain criteria.

If the RESULT is negative, I want to give it a score of 1, If the RESULT is less than 30, a score of 2, less than 100 a score of 3 and greater than 100 a score of 4

I have tried using the CASE statement but cannot seem to get it to work; I searched on the topics about constraints but they always seem to have two arguments - I need 4

Answer 1

I have updated the answer, as more details were given in the question (and to fix some errors)

Please remember to change table_name and trigger_name to appropriate names :).

SOLUTION:

You should first of all add the third column to the table

ALTER TABLE table_name ADD COLUMN SCORE INT;

You should add the trigger to set the SCORE for the new rows:

DELIMITER $$
CREATE TRIGGER trigger_name BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN

    SET NEW.SCORE = CASE WHEN NEW.RESULTS < 0 THEN 1 WHEN NEW.RESULTS < 30 THEN 2 WHEN NEW.RESULTS < 100 THEN 3 ELSE 4 END;

END$$
DELIMITER ;

And you should initialize SCORE values for rows existing in the table

UPDATE      table_name t
    SET     t.SCORE = CASE WHEN t.RESULTS < 0 THEN 1 WHEN t.RESULTS < 30 THEN 2 WHEN t.RESULTS < 100 THEN 3 ELSE 4 END;

Hope it helps.