sql - android update database column based on the current column value

Question

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 a column A by adding one to it, how should I prepare the ContentValues values variable? I don't think the following would work.

cv.put("A", "A" + 1);

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

Answer 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 + 1 WHERE _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.