Android Update Database Column Based On The Current Column Value

In android, SQLiteDatabase has a update function update(String table, ContentValues values, String whereClause, String[] whereArgs) new values in put in values If I want to update

Solution 1:

If you'd execute a raw query, something like this should work to increment the current value in the column:

UPDATE table_name SET column_a = column_a +1WHERE _id =1

(where 1 is just an example to illustrate how to apply it to a specific row)

The same probably wouldn't work with ContentValues, since (as the name indicates) it takes the values to set the column to. That means it needs to have been evaluated before building the ContentValues, whereas with a raw query the value isn't evaluated until the query actually runs on the database.

You can of course retrieve the current value first and then increment that accordingly when issuing an update; that requires a select query first. Quite commonly though, you're working with objects in Java, where the column value for a row is bound up to a member field of the object. If you've got a setup like that, then you probably already have the current value at the moment you want to run an update query.

As such, it would just look somewhat like:

SomeObject object = ...;
cv.put("column_a", object.getSomeValue() + 1);

(where I'm assuming object.getSomeValue() will return an int)

---

// edit: here's some more examples for the raw query approach:

• SQLite - increase value by a certain number

---

// edit2: You've edited your original question and added:

I can sure run execSQL with raw sql, but it does not return num of row updated

If knowing how many rows the query changed is a must, then you can potentially leverage the changes() function. It still means you're going to have to run a second query though.

SELECT changes() FROM table_name

The docs say:

The changes() function returns the number of database rows that were changed or inserted or deleted by the most recently completed INSERT, DELETE, or UPDATE statement, exclusive of statements in lower-level triggers. The changes() SQL function is a wrapper around the sqlite3_changes() C/C++ function and hence follows the same rules for counting changes.

Alternatively, you could look into the rawQuery() method that takes an SQL statement and returns the result as a Cursor. Not sure if it that even works for an update query, or whether the result would be anything sensible, but if you're really lucky, you may find that Cursor.getCount() gives you the number of affected rows.

Solution 2:

To expand upon @MH's solution, there actually is a way to do a raw update AND get back the number of rows updated (because I'm doing the same thing in one of my projects). You have to use a compiled SQLiteStatement and then call the method executeUpdateDelete(). From the documentation:

public int executeUpdateDelete ()

Execute this SQL statement, if the the number of rows affected by execution of this SQL statement is of any importance to the caller - for example, UPDATE / DELETE SQL statements.

Returns the number of rows affected by this SQL statement execution.

See the following sample code where I add a new column to my table and then update each column similarly to how you were asking:

db.beginTransaction();
try {
    db.execSQL("ALTER TABLE " + TABLE_NAME + " ADD COLUMN "
            + COLUMN_NAME_LOCALTIME + " INTEGER");
    StringstmtString="UPDATE " + TABLE_NAME + " SET "
            + COLUMN_NAME_LOCALTIME + "="
            + COLUMN_NAME_TIME + "+ (" + tzOffset + ")";
    SQLiteStatementsqlStmt= db.compileStatement(stmtString);
    introws= sqlStmt.executeUpdateDelete();
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}

I'm using a transaction here because in case I add the new column but CANNOT update the values, I want everything to rollback so I can attempt something else.