CODEWITHSUNDEEP
androidsql-updateandroid-sqlite
Ari
Ari

Reputation: 37

how to update a row with arithmetic expression in Sqlite android

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?

Upvotes: 2

Views: 488

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

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();

Upvotes: 1

Shreshth Neema
Shreshth Neema

Reputation: 119

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.

Upvotes: 0

Related Questions