When you store something that "looks like a number" in a column with numeric
affinity in the table declaration, the value is converted to a numeric type.
That is, if the "something that looks like a number" can be stored as an
integer, then it is stored as an integer (with no decimal point and no decimal
places). If the "something that looks like a number" cannot be stored as an
integer but can be stored as a floating point number, then that is how it is
stored. So, if you store the string '10.0000000' in a numeric affinity column,
the integer 10 is stored. If you store the string '25.100000' in a numeric
affinity column, then it is stored as the floating point value 25.1.
So your statement:
> UPDATE Transactions SET Value=Value||0 WHERE substr(Value,-2,1)='.'
does nothing other than convert a floating point value (say 10.1) to a text
representation, then appending a '0' to that text so it is now '10.10'. When
that text value is stored back into the numeric affinity column it is converted
back to the floating point value 10.1.
The select statement:
>SELECT Value||0 FROM Transactions WHERE substr(Value,-2,1)='.'
is simply showing you the text values before they are converted back into the
original floating point value. To see what will be stored in the database
itself you probably need something like:
>SELECT cast(Value||0 as numeric) FROM Transactions WHERE substr(Value,-2,1)='.'
Displaying values with 2 decimal places is an "output to the user by the
application" problem and you need to address it there (in the application --
right before displaying the value). For example, you can use printf("%.2f",
value) or its equivalent in your application language to format the output to
your requirements for viewing.
SQLite itself is working as designed.
---
Theory is when you know everything but nothing works. Practice is when
everything works but no one knows why. Sometimes theory and practice are
combined: nothing works and no one knows why.
>-----Original Message-----
>From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>bounces at mailinglists.sqlite.org] On Behalf Of Peter Haworth
>Sent: Wednesday, 11 March, 2015 17:28
>To: sqlite-users at mailinglists.sqlite.org
>Subject: [sqlite] UPDATE Problem
>
>I have a table, Transactions, with a column , Value, of type NUMERIC.
>The
>Value column is supposed to have 2 decimal places in all rows but some
>have
>only one.
>
>To correct this I issued the following command:
>
>UPDATE Transactions SET Value=Value||0 WHERE substr(Value,-2,1)='.'
>
>No errors on execution but nothing was changed.
>
>To help me figure out why that didn't work, I issued the following
>command:
>
>SELECT Value||0 FROM Transactions WHERE substr(Value,-2,1)='.'
>
>This time, all the values with only 1 decimal place were listed with a
>trailing zero added.
>
>On the face of it, the WHERE statement works fine in a SELECT statement
>but
>does not find any rows in an UPDATE statement. Either that or the SET is
>not calculating the correct value.
>
>Is this a bug or am I missing something?
>_______________________________________________
>sqlite-users mailing list
>sqlite-users at mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
