SQL Problem

[shadowskill]

Hello good day, this is what my Database looks like and my problem is i cannot figure out what SQL Query i should use.

I have already solve the part where my evaluation would become "Done" and "Must Be Taken" and here are my Queries 

 

UPDATE `column_table` SET `Evaluation`= 'Done' WHERE  `Remarks` = 'PASSED'

UPDATE `column_table` SET `Evaluation`= 'Must be taken' WHERE `SubjectPrerequisite ` = 'None' AND `Remarks` = 'FAILED'

UPDATE `column_table` SET `Evaluation`= 'Must be taken' WHERE `SubjectPrerequisite ` = 'None' AND `Remarks` = 'No Grade'

 

The problem is my last query where it will UPDATE the database to "Cannot be taken" when the SubjectPrerequisite's Remarks = FAILED or Grade = 'No Grade'

 

column_table

______________________________________________________________________________________

SubjectName | SubjectPrerequisite |   Grade   |     Remarks      |       Evaluation

______________________________________________________________________________________

Fil 1             |     None                   |               |  No Grade        |      Must be taken

 

Eng 1           |     None                   |  90         | PASSED          |      Done

 

Eng 2           |     Eng 1                  |  74         |  FAILED           |       Must be taken

 

Eng 3           |     Eng 2                  |              |  No Grade         |      Cannot be taken

 

Fil 2             |     Fil 1                    |               |  No Grade        |      Cannot be taken

______________________________________________________________________________________

 

Please tell me if your having a hard time understanding my explanation guys. Thank you very much

[Barand]

Is that the full table or is there a "student_id" (or similar) column also?

[Barand]

Assuming there is, try something like

 

 

UPDATE exam
LEFT JOIN exam as prereq
    ON exam.student_id = prereq.student_id
    AND prereq.subjectName = exam.SubjectPrerequisite
SET exam.Evaluation =
    CASE
    WHEN prereq.Remarks IN  ('FAILED','No Grade')
        THEN 'Cannot be taken'
    WHEN exam.Remarks = 'PASSED'
        THEN 'Done'
    ELSE 'Must be taken'
    END;