ID #1153

Tables with Floating Point Datatypes Fail to Update

Floating Point datatypes in MySQL are FLOAT, DOUBLE and (before MySQL 5.03) DECIMAL.

Those datatypes are not stored as exact values inside computer architecture. What you can see on the screen usually is not the exact value of the number. More details in MySQL documentation here:


Due to this there are restrictions in working with tables having such types in SQLyog.

1) If table having such column does not have a Primary Key updation of rows will likely fail from both the SQLyog DATA and RESULT tab
2) If table having such column uses that column for defining the PK then also updation of rows will likely fail from both the SQLyog DATA and RESULT tab
3) Also if table has such column and it is NOT used for the definition of the PK, updation from the RESULT tab may also fail if the SELECT query used to populate that RESULT tab does not contain all columns of the PK.

The explaination is that when updating SQLyog will generate an SQL statement like,
UPDATE mytable SET ... WHERE {where condition}

Whenever possible SQLyog will use the PK in the WHERE statement (...WHERE pkcolumn = some_value). If this is not possible SQLyog will generate a where_condition matching (and logically AND'ing) all columns in the result tab (...WHERE 1stcolumn = some_value AND 2ndcolumn = some_other_value etc.).  The 2nd statment here will likely fail to match the value as it is stored on the server for floating point types. And even the 1st statement also will if pk_column is a Floating Point datatype for the same reason.

When using Floating Point datatypes there should always be a PK based on non-floating column(s) and the PK-column(s) should be included in every SELECT generating a result set used for updating the table.

Tags: -

Related entries:

You can comment this FAQ