asked
sql update issue
Hi , I have a situation here. When i run my update for 30 million rows it updates the a field incorrectly for 5 records
only.It is a float type field .I am calculating a sum of a values and updating it in that field. If my expected value is 86.76 , the value that gets updated is 86.75 If i update using the same query only for that Record it gets updated correctly. Infact if i select using the same query i get to see the correct value. I am doing this update as a transaction. I am not sure how this is possible? Kindly share ur suggestions.I will try to share a similar query. Thanks, Kannan
Float types are allowed to have rounding differences. There might be some internals-based reason why you get a repeatable outcome, but you can't trust a non-precise data type to be precise. If you do want a precise value, I'd recommend switching from floats to decimals. You should then get consistent results. [This][1] is a relatively older document, but it goes into the topic in great theoretical detail, so it might be interesting. [1]:
http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html
thanks Kevin, That was very useful. But i get this issue consistently for the same columns and not for anyother columns.
Computer random isn't real-life random. You might see consistent results when running things in particular ways, but even those consistently different results aren't guaranteed: with a different data distribution or one of a number of other factors, you might end up with differences. If your other columns are floats and you're doing imprecise calculations with floats, you might just be getting lucky in not seeing it more often. But you can't count on very specific, precise results if you're using imprecise values. It might work today, and maybe well into the future, but there's no guarantee.
9 People are following this question.
