Delete rows from sqlite3 DB not in a list of objects

Question

I have a list containing some 'product' objects and want to delete all the products row in the DB that are not in that list based on their unique attribute serialNumber.

Example, if my list contain:

product A
product B
product C

My DB contain:

product A
product B
product C
product D

Then I want to remove product D from the DB.

This is my list:

List<Product> products;

I'm thinking of looping on the list to create a string array serialNumbers of serialNumber.

String[] serialNumbers;

for (int i=0; i<products.size(); i++){
    //Insert serialNumbers in Array
}

and then something like:

database.delete(MySQLiteHelper.TABLE_PRODUCTS, MySQLiteHelper.COLUMN_SERIALNUMBER + " NOT IN " + SerialNumbers, null);

How could I do the database query?

Juangcg · Accepted Answer · 2013-08-29 21:47:25Z

5

You can build the inQuery like this:

StringBuilder inQuery = new StringBuilder();

inQuery.append("(");
boolean first = true;
for (String item : serialNumbers) {
    if (first) {
        first = false;
        inQuery.append("'").append(item).append("'");
    } else {
        inQuery.append(", '").append(item).append("'");
    }
}
inQuery.append(")");

then use it:

database.delete(MySQLiteHelper.TABLE_PRODUCTS, MySQLiteHelper.COLUMN_SERIALNUMBER + " NOT IN " + inQuery.toString(), null);

answered Aug 29, 2013 at 21:47

Juangcg

1,0489 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

Delete rows from sqlite3 DB not in a list of objects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related