how to update a row with arithmetic expression in Sqlite android

Question

What I want is to add and remove new value to the existing value in Sqlite android. What I've read from the android documentation is this method, but it's not working in my case cause it's removing the existing value with the new one that's being provided.

here's my code:

public boolean updateCigaretteStock(String cigaretteName, int quantity, int totalCost) {
    ContentValues values = new ContentValues();
    values.put(CigaretteStockEntry.COLUMN_QTY, quantity);
    values.put(CigaretteStockEntry.COLUMN_TOTAL_COST, totalCost);

    String whereClause = CigaretteStockEntry.COLUMN_CIGARETTE_NAME + " = ?";
    String[] whereArgs = {cigaretteName};

    int rowAffected = writableDatabase.update(CigaretteStockEntry.TABLE_NAME, values, whereClause, whereArgs);
    return (rowAffected > 0);
}

I've also tried to modify the method:

 public boolean updateCigaretteStock(String cigaretteName, int quantity, int totalCost) {
    ContentValues values = new ContentValues();
    values.put(CigaretteStockEntry.COLUMN_QTY, CigaretteStockEntry.COLUMN_QTY + " + " + quantity);
    values.put(CigaretteStockEntry.COLUMN_TOTAL_COST, CigaretteStockEntry.COLUMN_TOTAL_COST + " + " + totalCost);

    String whereClause = CigaretteStockEntry.COLUMN_CIGARETTE_NAME + " = ?";
    String[] whereArgs = {cigaretteName};

    int rowAffected = writableDatabase.update(CigaretteStockEntry.TABLE_NAME, values, whereClause, whereArgs);
    return (rowAffected > 0);
}

But it isn't working too. It showing the value in the columns for example :

(quantity + new value)
(total_cost + new value)

Any help?

Tim Biegeleisen · Accepted Answer · 2018-12-10T08:45:33.493

1

If I understand your question correctly, you are trying to increment certain columns values along the lines of the following query:

UPDATE CigaretteStock
SET
    qty = qty + ?,
    totalcost = totalcost + ?;

The problem with the particular API you are using is that it only allows for clobbering (completely overwriting) a value in a given column. But, you want to actually use the value which is already there as part of the update.

In this case, I would suggest just using SQLite Android's prepared statement API:

String sql = "UPDATE CigaretteStock ";
sql += " SET " + CigaretteStockEntry.COLUMN_QTY + " = " +
    CigaretteStockEntry.COLUMN_QTY + " + ?, ";
sql += CigaretteStockEntry.COLUMN_TOTAL_COST + " = " +
    CigaretteStockEntry.COLUMN_TOTAL_COST + " + ?";

SQLiteStatement statement = db.compileStatement(sql);

statement.bindDouble(1, quantity);
statement.bindDouble(2, totalCost);

int numberOfRowsAffected = statement.executeUpdateDelete();

edited Dec 10 '18 at 08:45

answered Dec 10 '18 at 06:51

Tim Biegeleisen

502,043
27
286
360

@Ari Sorry, it should be `bindDouble`, I think. – Tim Biegeleisen Dec 10 '18 at 07:12
You need to include the WHERE statement so 3 args. **BindAllArgsAsString** would also work. e.g. `String[] arguments = new String[]{String.valueOf(quantity),String.valueOf(totalCost),cigaretteName}; SQLiteStatement statement = mDB.compileStatement(sql); statement.bindAllArgsAsStrings(arguments);` – MikeT Dec 10 '18 at 07:32
@Tim but it also troubled me a bit. I just copied and pasted ur answer n then found error saying no such table, then I included my table. N `SET` keyword, there should be space before and after it. Could you please update ur answer, so whoever is facing the same issue will not encounter any errors? – Ari Dec 10 '18 at 08:38
I have made that change. Glad I could help you. – Tim Biegeleisen Dec 10 '18 at 08:45
@Tim thanks man really n I remember making a similar app a long time ago that used just a raw query like `UPDATE TABLE_NAME SET COLUMN_1 = COLUMN_1 + NEW VALUE AND COLUMN_2 = COLUMN_2 + NEW VALUE WHERE CIGARETTE_NAME = "Pine"` could it work n should I use it? – Ari Dec 10 '18 at 10:02
Yes, it should _work_, but if you are doing any concatenation at all it is bad because because there might be some SQL injection happening. – Tim Biegeleisen Dec 10 '18 at 10:05
I'm just making an app for my personal use, which will manage my business n give me clarity. So no worries about sql injection. – Ari Dec 10 '18 at 11:16

score 0 · Answer 2 · answered Dec 10 '18 at 07:49

Try modifying your code as below:

public boolean updateCigaretteStock(String cigaretteName, int quantity, int totalCost) {
    ContentValues values = new ContentValues();
    values.put(CigaretteStockEntry.COLUMN_QTY, quantity + "");
    values.put(CigaretteStockEntry.COLUMN_TOTAL_COST, totalCost  + "");

    String whereClause = CigaretteStockEntry.COLUMN_CIGARETTE_NAME + " = ?";
    String[] whereArgs = {cigaretteName};

    int rowAffected = writableDatabase.update(CigaretteStockEntry.TABLE_NAME, values, whereClause, whereArgs);
    return (rowAffected > 0);
}

above code overwrite old value with new one. If you want to add new value in old than you need to first read old value than add it to new one & than pass in content value.

how to update a row with arithmetic expression in Sqlite android

2 Answers2