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-12 05:11:10Z

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, 2011 at 5:11

answered Jul 12, 2011 at 5:03

Mat

208k41 gold badges407 silver badges423 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tyler Collier Over a year ago

Is that method available in the Android APIs?

Mat Over a year ago

It's a sqlite function. You use it in select queries - independent of the language you're using.

Tyler Collier Over a year ago

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!

IgorOK Over a year ago

@Tyler Collier Could you provide an example how to select affected rows after update? Thanks!

Tyler Collier Over a year ago

@Gosha, click the changes() link in Mat's answer for help on changes().

Community · Accepted Answer · 2017-05-23 11:54:30Z

12

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

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Aug 26, 2013 at 9:36

Pang

10.2k146 gold badges87 silver badges126 bronze badges

1 Comment

david.perez Over a year ago

I would use this simpler approach: cursor.getLong(0).

serv-inc · Accepted Answer · 2017-09-02 13:12:24Z

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 2, 2017 at 13:12

serv-inc

38.8k9 gold badges195 silver badges215 bronze badges

answered Sep 12, 2015 at 11:57

Baker

28.4k13 gold badges111 silver badges116 bronze badges

Comments

Fedir Tsapana · Accepted Answer · 2016-09-14 10:28:38Z

2

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.

answered Sep 14, 2016 at 10:28

Fedir Tsapana

1,37117 silver badges22 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

5 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related