Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Update record in DB fails

guy.b's profile image guy.b posted 3 weeks ago in General Permalink

when I try to update a record in the Data view of the table I get an error "0 rows updated when that should have been 1" I noticed that Heidi generates an Update / Set SQL statement with all the fields and values in the Where statement, when evaluating the where it fails, for example: i have a column with float (unsinged) value, the value is originaly 4.69, when the SQL does the compare:

SELECT * FROM `Table` WHERE  `Param`=4.69; -> doesn't return the record
SELECT * FROM `Table` WHERE  `Param`>4.69000001; -> returns the record, although the value is 4.69
SELECT * FROM `Table` WHERE  `Param`><4.69000001; -> doesn't return the record

this in turn fails the update statment - as it evaluate all parameters including the above.

I wonder why Heidi doesn't use the "Key" fields only for the "Where" statement - why does it look for the full param <-> value set ? is this configurable ? I wonder why MMySQL fails to evaluate the "where" statement correctly for floating point.

guy.b's profile image guy.b posted 3 weeks ago Permalink

Changing the field from float to decimal will solve the issue - but I still wonder why Heidi needs to "check" all the fields in the update statement instead only the keys (in case unique key exists).

ansgar's profile image ansgar posted 3 weeks ago Permalink

HeidiSQL needs at least a unique key for such updates, and that key must not allow NULLs. Even better is a primary key.

My guess is your key is a unique key which allows NULLs, or a non-unique key?

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.