Very Large Number Of Decimal Places

rocketman

Some unchecked rounding errors have crept into my system which results in some items with very large number of decimal places - something like 5.0000000000006475. The problem is Excel won't deal with such big numbers, it converts them to text by placing an apostrophe in front to the number (and also changes some to an "E" notation.
So I have two questions,

1) to avoid interrogating 10's of 0000' of transactions one by one is there a FIND statement that I can construct to find the ones I need to fix.

2) Will this statement work once I've found them BO.number_attribute=ROUND (BO.number_attribute,2)

Alternatively - might there be a way in MySQL to fix this - something like a TRIM function

rocketman

"2) Will this statement work once I've found them BO.number_attribute=ROUND (BO.number_attribute,2)"

Ok - I can't seem to do this because the production Log reports "Value was not changed because it was the same as the old value" although if I make a small change - even £0.01 it works.
So Question. Is there a command to force a write to the database even if the calculation gives the same result? But the good news is, I've found a way in Excel to overcome the E notations

rocketman

Data took seconds to clean up directly in MySql using

UPDATE [table] SET [column] = ROUND(column,2)

Note to self …. Sometimes it’s best to go back to your roots