CODEWITHSUNDEEP
mysqlsqlsql-update
Austin Johnston
Austin Johnston

Reputation: 219

subtraction returning null value mysql

UPDATE student as s
LEFT JOIN takes as t 
     ON s.ID = t.ID
LEFT JOIN course as c 
     ON t.course_id = c.course_id
SET s.tot_cred = s.tot_cred - c.credits
WHERE t.grade = 'F' OR t.grade IS NULL

I am trying to update tot_cred in student by subtracting the credit value of any class the student has failed, grade in takes relation = 'F', or is currently taking, grade in takes relation IS NULL.

The query above however sets tot_cred to NULL for any of the students who meet this criteria and I cannot figure out why.

I apologize if this has been asked before, I tried to search for something relevant but couldn't find many issues related to subtraction. I am new to stackoverflow. Thank you all for your help.

Upvotes: 4

Views: 1792

Answers (2)

Iswanto San
Iswanto San

Reputation: 18569

You can use COALESCE like @JW answer, or use IFNULL:

UPDATE student as s
LEFT JOIN takes as t 
     ON s.ID = t.ID
LEFT JOIN course as c 
     ON t.course_id = c.course_id
SET s.tot_cred = s.tot_cred - IFNULL(c.credits, 0)
WHERE t.grade = 'F' OR t.grade IS NULL

Upvotes: 4

John Woo
John Woo

Reputation: 263733

add COALESCE on c.credits

set s.tot_cred = s.tot_cred - COALESCE(c.credits,0)

Upvotes: 4

Related Questions