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.
Rate this post
4 of 5 based on 7021 votesComments