CODEWITHSUNDEEP
mysqlsql
jmenezes
jmenezes

Reputation: 1926

Update set col to 0.00 if null or calculated value

I'm trying to set a column to 0.00 if it's NULL, or, the calculated value if it's not null.

In the table below, the max score may not always be set, and the perCent col sometimes ends up showing NULL, which looks odd in the front end display.

I was trying the following sql, which does not throw any error, and does not work either, which means something is wrong.

How can I set this right?

Table

"studentId" "maxScore"  "studentScore"  "stdPerCent"
"1"         "100"       "25"            "25.00"
"2"         "0"         "0"             NULL

Sql

update scores SET stdPerCent = if(NULL,'0.00',TRUNCATE(100*studentScore/maxScore,2));

Upvotes: 0

Views: 81

Answers (1)

Sam
Sam

Reputation: 1366

I find COALESCE to be easier to use (and to read) than the CASE WHEN:

UPDATE scores 
SET stdPerCent = COALESCE(TRUNCATE(100*studentScore/maxScore,2), '0.00')

Edit: and if you are just trying to fill the NULL fields and you know non-NULL fields were already calculated properly, this would make more sense:

UPDATE scores 
SET stdPerCent = '0.00'
WHERE stdPerCent IS NULL

Upvotes: 2

Related Questions