[FIXED] Android update all records SQLite

Issue

my table contains 7 records. With the update method, I want to update a column that has INTEGER values . with a for loop, I want to update all records with a sequence of numbers, but it seems not work properly, because all records are updated with the last of the loop (7). Maybe the error is in the WHERE clause? Thanks for your advice.

SQLiteDatabase datab = new DatabaseHelper(getActivity()).getReadableDatabase();

ContentValues cv = new ContentValues();
for(int i = 1; i<8; i++){
    cv.put(MyTable.NUMERAZIONE, i);
    datab.update(MyTable.TABLE_NAME, cv, MyTable.NUMERAZIONE + "!="+i, null);
}
datab.close();

I also tried without a WHERE clause, but I get the same result:

datab.update(MyTable.TABLE_NAME, cv, null, null);

Records are 7, I want to update all records, without any clause.

Solution

Consider the SQL that will be run by your call to update(). Let's take the first iteration (i = 1):

UPDATE table_name SET numerazione = 1 WHERE numerazione != 1

Any row where numerazione is not 1 will have it set to 1, so the result is that every row has numerazione = 1. Now we run the next iteration (i = 2):

UPDATE table_name SET numerazione = 2 WHERE numerazione != 2

Any row where numerazione is not 2 will have it set to 2. But after the previous iteration, all rows have numerazione = 1, so this statement will update all the rows. Same thing happens for the remaining iterations, with the last one setting numerazione to 7 for all rows.

You need to find some other WHERE clause or some other way to update your rows. One solution is to build the WHERE clause using the NOT IN operator and making the list larger each time. The first would have NOT IN (1), the second would have NOT IN (1,2), and so on. Since you only have to do this a small number of times, it won't be too bad for performance.

private String buildWhere(int number) {
    StringBuilder builder = new StringBuilder(MyTable.NUMERAZIONE).append( "NOT IN (1");
    for (int i = 2; i <= number; i++) {
        builder.append(',').append(i);
    }
    builder.append(')');
    return builder.toString();
}
...
datab.update(MyTable.TABLE_NAME, cv, buildWhere(i), null);

This should result in the following statements:

UPDATE table_name SET numerazione = 1 WHERE numerazione NOT IN (1)
UPDATE table_name SET numerazione = 2 WHERE numerazione NOT IN (1,2)
...
UPDATE table_name SET numerazione = 7 WHERE numerazione NOT IN (1,2,3,4,5,6)

Answered By - Karakuri
Answer Checked By - Clifford M. (JavaFixing Volunteer)