SQLite Android - Updating (adding up) existing value with new one

Question

I am new to android and i am trying to use these code to update an entry's value:

"Update " + TableIncomeCategory + " Set " + Income_Cat_currentAmount + " = " + "'" + Income_Cat_currentAmount + "'"
                        + "+" + "'"+tran.getAmount()+"'" + " where " + Income_category_name + " LIKE " + "'" + tran.getCategory() + "'");

I am trying to add up the current amount (Income_Cat_currentAmount) with the new value (tran.getAmount()) the code is running without crashing but every time I run it, the new value replaces the old one rather than adding up together. Can anyone tell me why and how to solve it? Many Thanks!!

Answer 1

Your problem is the formatting. The SQL command ends up like this:

Update IncomeCategoryTab
Set currentAmount = 'currentAmount'+'123'
where category_name LIKE 'some category'

This is wrong because you are appending one string to another.

You must not quote column names, and you should not quote values that are not strings:

Update IncomeCategoryTab
Set currentAmount = currentAmount + 123
where category_name LIKE 'some category';

In Java, you should use parameters for string values (like the category name) to avoid having to correctly quote them (which becomes more complex if they can contain quotes):

db.execSQL("Update "+TableIncomeCategory+
           " Set "+Income_Cat_currentAmount+" = "+Income_Cat_currentAmount+" + "+tran.getAmount()+
           " where "+Income_category_name+" LIKE ?;",
           new Object[]{ tran.getCategory() });