How to add a set of different values in one column(MySQL)?-CodePudding

Do not judge strictly, but I can not figure it out in any way My table:

CREATE table courses  (id INT PRIMARY KEY AUTO_INCREMENT,
    -> faculty VARCHAR(55) NULL,
    -> number INT(10) NULL,
    -> diff VARCHAR(10) NULL);

mysql> select * from courses;

enter image description here

Target. Inject values ('ez', 'mid', 'hard') into diff column. For exampl, im trying this:

mysql> INSERT courses (diff) VALUES ('ez');

mysql> UPDATE courses SET faculty = 'chem', number = 2, diff = 'mid';

Add rows with empty id(values NULL). PLZ help me! I want to get this result

 ---- --------- -------- ------ 
| id | faculty | number | diff |
 ---- --------- -------- ------ 
|  1 | bio     |      1 | ez   |
|  2 | chem    |      2 | mid  |
|  3 | math    |      3 | hard |
|  4 | geo     |      4 | mid  |
|  5 | gum     |      5 | ez   |
 ---- --------- -------- ------

CodePudding user response：

You can use a case expression in an UPDATE statements:

UPDATE courses 
SET diff=CASE 
    WHEN faculty in ('bio', 'gum') THEN 'ez' 
    WHEN faculty in ('chem', 'geo') THEN 'mid' 
    WHEN faculty = 'math' THEN 'hard' 
    END;