SQLite: bulk update a 'boolean' column

Question

What is the standard approach to update a boolean column (i.e. a categorical binary INTEGER value) in a SQL (SQLite) table?

^{(here binary is intended to mean an INTEGER that takes two values only: 0 and 1, hence the quotes in the question title)}

Given a table:

CREATE TABLE types (
    id INTEGER NOT NULL PRIMARY KEY, 
    name TEXT, 
    enabled INTEGER DEFAULT 1)

And a list of objects which can have an enabled/disabled state and which are represented as two lists:

List<Long> idsEnabled = new ArrayList<>();
List<Long> idsDisabled = new ArrayList<>();

Would this be the correct way to store the enabled state (i.e. 0 or 1)?

SQLiteDatabase db = dbHelper.getWritableDatabase();

// upd: NOT WORKING because rawQuery does NOT work for modifying data, 
// must use execSQL
db.rawQuery("UPDATE types set enabled=1 where id in (" 
    + TextUtils.join(",", idsEnabled) + ")", null);
db.rawQuery("UPDATE types set enabled=0 where id in (" 
    + TextUtils.join(",", idsDisabled) + ")", null);

Is there a better, more efficient, OOTB way to do this? My assumption is that updating each row one by one would be the worst case.

The question is not necessarily android-specific, although if there is some android-specific API for doing this it would be helpful.

Answer 1

The method rawQuery() is not the way to update the table.
It is used to return rows in the form of a Cursor.
In this case you must use execSQL():

if (idsEnabled.size() > 0 || idsDisabled.size() > 0) {
    String ids1 = TextUtils.join(",", idsEnabled);
    String ids0 = TextUtils.join(",", idsDisabled);
    String sql =
            "UPDATE types " +
            "SET enabled = CASE " +
            (idsEnabled.size() > 0 ? "WHEN id IN (" + ids1 + ") THEN 1 " : "") +
            (idsDisabled.size() > 0 ? "WHEN id IN (" + ids0 + ") THEN 0 " : "") +
            "ELSE enabled " +
            "END";
    db.execSQL(sql);
}

The above statement uses a CASE expression to determine the value that will update the current value of the column.

If you want to update just a small part of the table then it would be more efficient to include a WHERE clause in the UPDATE statement, so you can omit the ELSE part of the CASE expression:

if (idsEnabled.size() > 0 || idsDisabled.size() > 0) {
    String ids1 = TextUtils.join(",", idsEnabled);
    String ids0 = TextUtils.join(",", idsDisabled);

    String idsAll = "";
    if (idsEnabled.size() == 0) idsAll = ids0;
    else if (idsDisabled.size() == 0) idsAll = ids1;
    else idsAll = ids1 + "," + ids0;

    String sql =
            "UPDATE types " +
            "SET enabled = CASE " +
            (idsEnabled.size() > 0 ? "WHEN id IN (" + ids1 + ") THEN 1 " : "") +
            (idsDisabled.size() > 0 ? "WHEN id IN (" + ids0 + ") THEN 0 " : " ") +
            "END " +
            "WHERE id IN (" + idsAll + ")";
    db.execSQL(sql);
}