Adding bonus to students score and should not exceeds the full mark-CodePudding

There is a section in my school web app that allows me to add bonus score for all students of a specific class at once. The math problem I am struggling to solve is when the student got full mark and nothing can be add up. also the same issue happened when the total score valve will exceeds the final mark limit !

so, using the following MySQL query, how can I update all student scores which will not at any circumstances exceeds the limit.

for Example: course limit is 60 and the current student final score is 59 of 60 and we would line to add bonus of 4 marks to all students !?

UPDATE
    students
SET
    final_score = final_score   4
WHERE 
    studentid = 201

Thanks in advance

CodePudding user response：

You can use LEAST() for this, which will apply the smallest of a set of values. This will require your query to know what the max score is.

UPDATE
    students
SET
    final_score = final_score   LEAST(modifier, max_score - final_score)
WHERE 
    studentid = 201

Where of course max_score - final_score will be the exact amount to reach a perfect score. If that exact amount is less than modifier, then the exact amount will be applied instead of modifier.

If you want to subtract modifier, and avoid a negative result, you can do this

UPDATE
    students
SET
    final_score = final_score - LEAST(modifier, final_score)
WHERE 
    studentid = 201

CodePudding user response：

Would something like this work for you?

UPDATE
    students
SET
    final_score = final_score   
    (CASE
        WHEN final_score = 57 THEN 3
        WHEN final_score = 58 THEN 2
        WHEN final_score = 59 THEN 1
        ELSE 4
    END)

CodePudding user response：

Seems like a homework question?

Clearly you don't want to give everyone 4, because it could exceed the course limit if someone scored i.e. an 58. But a student scoring 58 should get the 2 points extra right to get the max score right?

This means you can't filter student scores in the WHERE statement, using something like WHERE final_score < 60. Instead, try a way of using the number 60 in your SET statement so there is no way to surpass 60.

Good luck!