Tables With Floating Point Datatypes Fail To Update

[andre_ss6]

I know that there is an entry on the FAQ about this: http://faq.webyog.com/content/29/162/en/tables-with-floating-point-datatypes-fail-to-update.html

If you have a table with a float column, SQLyog will fail to update entries in that table if it doesn’t have a PK. However, the problem is in the SQL statement SQLyog generates, which puts the float values in quotes, like this:

 

UPDATE `table`.`column` SET `columnToChange` = NULL WHERE  `floatColumn` = ‘0.3’

If you try to run this statement manually, it’ll also fail to update. If you, however, simply remove the quotes in the 0.3, like this:

 

UPDATE `table`.`column` SET `columnToChange` = NULL WHERE  `floatColumn` = 0.3

 

Then it works.

Why doesn’t SQLyog do this automatically? Is there any problem or difficulty in removing the quotes?

Thanks in advance, Andre.

[maryam.dairkee]

 

Even without quotes it will not always update the table. This is not the problem of quotes but the Floating-point numbers. Floating point numbers are not stored as exact value,they are approximated. For more details read this http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html. That’s why we suggest to use primary key. Here again, primary key shouldn’t be a floating point type, otherwise same problem will be faced.

[Author]

Posts