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?