John Davidson

Message:

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

---

My Answer:
You can use a CASE statement in your UPDATE query to check if adding the bonus will exceed the limit. Here is an example query:

sql
UPDATE students
SET final_score =
CASE
WHEN final_score + 4 > 60 THEN 60
ELSE final_score + 4
END
WHERE studentid = 201;


This query will update the final score of student with id 201 by adding 4 marks, but ensuring that the final score does not exceed the limit of 60. If the final score after adding the bonus would exceed the limit, it will be set to the limit instead.