CODEWITHSUNDEEP
mysql
Aneesh
Aneesh

Reputation: 1733

Why this weird MYSQL result in division?

I have a SQL query like:

UPDATE t_example 
SET column1=column2/column3 
where 'SOME CLAUSE'

Suppose column2 has value 7 and column3 has value 1, the column1 should be set to value 7 after this statement but its always 6 instead of 7. The result is always less by 1 for any pair of values where divisor is 1. I haven't checked for values other than 1 but why this behaviour?

Upvotes: 0

Views: 68

Answers (2)

Santosh
Santosh

Reputation: 17913

Try the ROUND() function from MYSQL as follows

NSERT INTO t_example SET column1=ROUND(column2/column3)

Upvotes: 0

juergen d
juergen d

Reputation: 204784

This is probably a floating-point inaccuracy.

Your column2 is probably a floating point and column1 is an int. If the outcome of the calculation is 6.999999999 then the result converted to int is 6.

If you need exact accuracy then you should use a fixed-point data type like decimal.

Upvotes: 3

Related Questions