Android SQLiteDatabase update multiple rows using where clause with IN or OR

Question

I have the following code which I was using to update a single row in my database:

public int updateSentFieldForIncomingMessage(IncomingMessage incomingMessage)
{
    SQLiteDatabase db;
    int numberOfRowsAffected;

    try
    {
        db = super.getWritableDatabase();
    }
    catch (SQLiteException e)
    {
        File dbFile = context.getDatabasePath(DATABASE_NAME);
        db = SQLiteDatabase.openDatabase(dbFile.getAbsolutePath(), null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
    }

    try
    {
        ContentValues values = new ContentValues();
        values.put(KEY_MESSAGE_SENT, true);

        numberOfRowsAffected = db.update(TABLE_INCOMING_MESSAGES, values, KEY_ID + " = ?", new String[]{String.valueOf(incomingMessage.getId())});
    }
    finally
    {
        db.close();
    }

    return numberOfRowsAffected;
}

This works fine but now I'd like to refactor the method so that instead of a single IncomingMessage parameter, it accepts a list of IncomingMessage. But when I get to this statement:

db.update(TABLE_INCOMING_MESSAGES, values, KEY_ID + " = ?", new String[]{String.valueOf(incomingMessage.getId())});

I'm not sure how to modify it so that I can specify multiple rows (via KEY_ID) in my WHERE clause. What I'd like to do is say something like KEY_ID IN ( <multiple row IDs here> ). Is that possible?

Blackbelt · Accepted Answer · 2015-09-23 18:11:50Z

5

Is that possible?

it is, but you will need a number of ? equals to the length of String[]. You can use a String builder to create such whereClause.

E.g.

db.update(TABLE_INCOMING_MESSAGES, values, KEY_ID + " IN ( ?, ?, ?, ?)", new String[]{String.valueOf(incomingMessage.getId()),  "id1", "id2", "id3"});

edited Sep 23, 2015 at 18:11

answered Sep 23, 2015 at 18:04

Blackbelt

158k31 gold badges308 silver badges310 bronze badges

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

3 Comments

bmt22033 Over a year ago

So if my list had 5 values in it, you mean something like KEY_ID + " = ? OR ? OR ? OR ? OR ?", is that correct?

Blackbelt Over a year ago

KEY_ID IN ( ?, ?, ?, ?), new String[] {"id0", "id1", "id2", "id3"}

bmt22033 Over a year ago

Ahh, ok. I've got it now. Thanks very much!

headuck · Accepted Answer · 2015-09-23 18:28:17Z

2

In case you have unknown number of arguments, you can also use:

String args = TextUtils.join(", ", arrayOfIds);

db.execSQL(String.format("UPDATE %s SET %s = true WHERE %s IN (%s);", 
    TABLE_INCOMING_MESSAGES, KEY_MESSAGE_SENT, KEY_ID, args));

answered Sep 23, 2015 at 18:28

headuck

2,81320 silver badges19 bronze badges

Collectives™ on Stack Overflow

Android SQLiteDatabase update multiple rows using where clause with IN or OR

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related