CODEWITHSUNDEEP
androidsqlite
Rahul Gupta
Rahul Gupta

Reputation: 5295

How to use WHERE IN clause in Android Sqlite?

I am trying to execute an single update query where i have to update a single field for many persons. Now the below query works :-

UPDATE PERSON SET ISSELECTED=1 WHERE ID IN (132,142,115,141,41,133,40,56,139,134,135,65,143,9,64,39,120,104,122,35,19,98,124,127,130,136,119,123,55,102,5,128,140,95,138,131,96,93,129,103,94,89,126,21,29,125,3,101,92,113,4,88,111,63,60,38,114,90,31,118,99,121,117,100,112,97,25,116,10,32,27,30,14,26,12,61,57,20,107,110,91,109,108,106,105,16,62,33,59,18,58,36,11,15,37,28,24,6,7,8,34,13)

But it does not return the number of updated rows when used with execSql or rawQuery

I am trying to form this query using the update method which returns the no. of rows affected

int rowsUpdatedSelected = db.update("PERSON", values, "ID" + " = ?", new String[] {id.toString()}); 
// where id is a StringBuilder object containing the ids like above

But this is not working.

Upvotes: 0

Views: 1431

Answers (4)

MysticMagicϡ
MysticMagicϡ

Reputation: 28823

You can try using sqlite3_changes() for this:

This function returns the number of database rows that were changed or inserted or deleted by the most recently completed SQL statement on the database connection specified by the first parameter. Only changes that are directly specified by the INSERT, UPDATE, or DELETE statement are counted.

So after your update statement, add this code:

Cursor cursor = null;
try
{
    cursor = db.rawQuery("SELECT changes() AS updated_row_count", null);
    if(cursor != null && cursor.getCount() > 0 && cursor.moveToFirst())
    {
        final long count = cursor.getLong(0);
        Log.d("LOG", "no. of updated rows : " + count);
    }
} catch(SQLException e)
{
    e.printStackTrace();
} finally
{
    if(cursor != null)
    {
        cursor.close();
    }
}

Hope this helps.

Upvotes: 0

fatboy
fatboy

Reputation: 2137

I didn't try this.. just give a try.. and let me know wethr its working or not..

int rowsUpdatedSelected = db.update("PERSON", values, "ID IN (?)", new String[] {id.toString()});

Upvotes: 0

samsad
samsad

Reputation: 1241

Try this code.

Put single quote on your in you IN data 

UPDATE PERSON SET ISSELECTED=1 WHERE ID IN ('132','142','115','14','1');

Upvotes: 0

Pankaj Kumar
Pankaj Kumar

Reputation: 83048

You can write a method to make IN query string and use that as you selectionArgs. Like below

selectionArgs = idArray; // Where id array will be String[] and will contain all of your ids
selection = "ID" + makeInQueryString(idArray.length);

Where makeInQueryString() is 

/**
 * Creates where string which can be used for IN query. It creates string
 * containing "?" separated by ",". This method can be used as below <br>
 * ColumnName + makeInQueryString(size) <br>
 * This will create IN query for provided column name.
 * 
 * @param size
 *            size of the items
 * @return IN query string of the form (?,?,?,?)
 */
public static String makeInQueryString(int size) {
    StringBuilder sb = new StringBuilder();
    if (size > 0) {
        sb.append(" IN ( ");
        String placeHolder = "";
        for (int i = 0; i < size; i++) {
            sb.append(placeHolder);
            sb.append("?");
            placeHolder = ",";
        }
        sb.append(" )");
    }
    return sb.toString();
}

Upvotes: 2

Related Questions