sqlite3 delete multiple rows using "WHERE column IN (?)"

Question

I'm trying to delete multiple rows with a single command:

String[] args = {"1143997,1144373,1144375,1144383,1144385,1144389"};
int n;
n = db.delete("mytable", "recno IN (?)", args);
Log.d(TAG, "Deleted " + n + " rows");

But it doesn't work. Neither did

db.execSQL("DELETE FROM mytable WHERE recno IN (?)", args);

But if I fetch the database file with adb pull, run sqlite3 from the command line, and type

DELETE FROM mytable WHERE recno in (1143997,1144373,1144375,1144383,1144385,1144389);

it works just fine. Any idea what I'm doing wrong?

TeChNo_DeViL · Accepted Answer · 2016-01-29 09:44:59Z

5

You need n comma separated ? for n arguments. Try something like:

db.execSQL("DELETE FROM mytable WHERE recno IN (?,?,?,...)", args);

You need one ? per argument and args should be an array of arguments, not a string. It should be like

String args[] = {arg1, arg2, arg3, ...};

What you can do is programmatically create the ? based on your number of arguments. Implement this function.

/**
 * Function to create the argument "?,?,?,.." string
 *
 * @param len The length of array of arguments
 */
String makePlaceholders(int len) {
    StringBuilder sb = new StringBuilder(len * 2 - 1);
    sb.append("?");
    for (int i = 1; i < len; i++) {
        sb.append(",?");
    }
    return sb.toString();
}

Then simply make your IN clause query as:

db.execSQL("DELETE FROM mytable WHERE recno IN (" + makePlaceholders(args.length()) + ")", args);

This should work well for your purpose. Hope this helps!

edited Jan 29, 2016 at 9:44

answered Jan 28, 2016 at 22:46

TeChNo_DeViL

7415 silver badges11 bronze badges

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

1 Comment

Edward Falk Over a year ago

OK, that's a little trickier than I was counting on, because the number of arguments could vary, meaning I have to build my "?,?,?..." string programatically. Let me think about that...

Community · Accepted Answer · 2017-05-23 12:23:34Z

1

Maybe your array is not well constructed.

You have this:

String[] args = {"1143997,1144373,1144375,1144383,1144385,1144389"};

But I think you want to do this:

String[] args = {"1143997","1144373","1144375","1144383","1144385","1144389"};

I think that your array contains only one element with all the IDs and you need an array with N elements, one per ID.

Look at this answer. It may help you.

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Jan 28, 2016 at 22:49

Ale Sanchez

5681 gold badge7 silver badges21 bronze badges

1 Comment

Edward Falk Over a year ago

That answer suggests using String.format to effectively generate execSQL("DELETE FROM mytable WHERE recno IN (1143997,1144373,1144375,1144383,1144385,1144389)"); and no whereArgs argument to execSQL(). I try to avoid passing arguments without the whereArgs argument because of SQL injection attacks, but I guess in this case I'm pretty confident that this can't happen.

Collectives™ on Stack Overflow

sqlite3 delete multiple rows using "WHERE column IN (?)"

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related