CODEWITHSUNDEEP
mysql
TheGreatCornholio
TheGreatCornholio

Reputation: 1533

MySQL - Warning: #1365 Division by 0

I'm using this query:

UPDATE memes SET upvotes = upvotes + 1, score = ROUND((upvotes * 100) / (upvotes + downvotes)) WHERE ((mature_upvotes / upvotes) * 100) < 15 AND upvotes > 8 AND upvotes < 13

and I get the warning

Warning: #1365 Division by 0

There are over 10k rows so I don't know which are affected. But how can this even be when (upvotes + downvotes) is never 0?

I've also updated one specific row which had mature_upvotes as 0 to see if that could cause the warning, but I didn't get it

Upvotes: 2

Views: 43

Answers (1)

Starship
Starship

Reputation: 752

Currently, you have

WHERE ((mature_upvotes / upvotes) * 100) < 15 AND upvotes > 8 AND upvotes < 13

So you calculate mature_upvotes/upvotes before you confirm that you have more than 8 and less than 13 upvotes. This means you haven't filtered out items with 0 upvotes, so in some cases you are dividing by 0. Making mature_upvotes 0 like you did also wouldn't cause this error, as you divide by upvotes, not mature_upvotes.

This can be easily solved, by putting the upvotes > 8 AND upvotes < 13 before you attempt the division, like this:

WHERE upvotes > 8 AND upvotes < 13 AND ((mature_upvotes / upvotes) * 100) < 15

Upvotes: 2

Related Questions