0

I am using following query in MYSQL to multiply value in colname by 1000. data type of colname is double.It works fine but for one value in table it is giving wrong output. value is 1066203.9 and after update value should be 1066203900 but output is 1066203899.9999999.

UPDATE tablename SET colname=colname*1000 WHERE id=1
hrishi
  • 1,610
  • 6
  • 26
  • 43
  • 1
    check datatype of your column.Might be out of range value. – Hikmat Sijapati Mar 01 '17 at 08:36
  • doubles are not precise, so what you see can be normal... see this question and answers: http://stackoverflow.com/questions/6831217/double-vs-decimal-in-mysql you might want to use decimal instead – fthiella Mar 01 '17 at 08:39
  • Perhaps the value you see before multiplying, 1066203.9, is actually rounded from1066203.89999999999 – PeteH Mar 01 '17 at 08:40
  • When I use 1066203.8 instead of 1066203.9 means .8 instead of .9 then it works fine – hrishi Mar 01 '17 at 08:53
  • @fthiella will it affect existing values if I change datatype from double to decimal – hrishi Mar 01 '17 at 09:00
  • @ PeteH value is 1066203.90 when I read it and inserted in database it stored as 1066203.9 – hrishi Mar 01 '17 at 09:06
  • yes, if you change the datatype from double to decimal there could be some rounding issues also... – fthiella Mar 01 '17 at 09:17
  • @fthiella I tried with decimal(10,2) but ouput is 99999999.99. – hrishi Mar 01 '17 at 09:17
  • `decimal(10,2)` means 10 digits, 2 of these after the decimal point (and thus 8 before it), so `99999999.99` is your maximal value. Just use more digits if you need more digits. – Solarflare Mar 01 '17 at 12:31

0 Answers0