Get updated rows count from SQLite in Android using a raw query?

Question

How can I get the number of rows updated using an update statement in SQLite in Android?

Note that I need to use some type of raw execute of a SQL statement, rather than do anything with ContentValues because I need a dynamic query. Thus I can't make use of the SQLiteDatabase.update() method. For example, I'm running something like

UPDATE my_table 
   SET field_name = field_name + 1

The methods I know of return void, e.g. SQLiteDatabase.execSQL(). SQLiteDatabase.rawQuery() returns a Cursor, but the cursor has zero rows and its count is always -1.

Mat · Accepted Answer · 2011-07-12T05:11:10.917

17

You could do your insert whichever way you want, and then do a select and use the changes() function to return the number of affected rows.

edited Jul 12 '11 at 05:11

answered Jul 12 '11 at 05:03

Mat

202,337
40
393
406

Is that method available in the Android APIs? – Tyler Collier Jul 12 '11 at 06:04
1

It's a sqlite function. You use it in `select` queries - independent of the language you're using. – Mat Jul 12 '11 at 06:07
2

Ah. Great! I'd imagine it's wise to wrap such calls with transactions. So you'd do 1) begin tran 2) do update 3) get changes 4) end transaction. It works, thanks! – Tyler Collier Jul 12 '11 at 06:15
@Tyler Collier Could you provide an example how to select affected rows after update? Thanks! – IgorOK Mar 24 '13 at 18:03
@Gosha, click the `changes()` link in Mat's answer for help on `changes()`. – Tyler Collier Mar 31 '13 at 02:43

score 12 · Answer 2 · edited May 23 '17 at 11:54

To expand on Mat's answer, here's the example code for getting the updated row count:

Cursor cursor = null;
try
{
    cursor = db.rawQuery("SELECT changes() AS affected_row_count", null);
    if(cursor != null && cursor.getCount() > 0 && cursor.moveToFirst())
    {
        final long affectedRowCount = cursor.getLong(cursor.getColumnIndex("affected_row_count"));
        Log.d("LOG", "affectedRowCount = " + affectedRowCount);
    }
    else
    {
        // Some error occurred?
    }
}
catch(SQLException e)
{
    // Handle exception here.
}
finally
{
    if(cursor != null)
    {
        cursor.close();
    }
}

I would use this simpler approach: `cursor.getLong(0)`. – david.perez Oct 23 '14 at 10:56 — david.perez, Oct 23 '14 at 10:56

score 5 · Answer 3 · edited Sep 02 '17 at 13:12

5

Expanding on Mat and Pang's answers...

Could we skip the Cursor and use simpleQueryForLong()?

e.g.

public long getChangesCount() {
    SQLiteDatabase db = getReadableDatabase();
    SQLiteStatement statement = db.compileStatement("SELECT changes()");
    return statement.simpleQueryForLong();
}

edited Sep 02 '17 at 13:12

serv-inc

35,772
9
166
188

answered Sep 12 '15 at 11:57

Baker

24,730
11
100
106

score 3 · Answer 4 · answered Sep 14 '16 at 10:28

You can use SQLiteStatement.executeUpdateDelete method for this:

SQLiteDatabase db = getReadableDatabase();
SQLiteStatement statement = db.compileStatement("[your sql here]");
int affectedRows = statement.executeUpdateDelete();

The same method used internally in SQLiteDatabase.update(...) methods.

Get updated rows count from SQLite in Android using a raw query?

4 Answers4

Linked

Related