How to bulk update SQLite table entries in Flutter-CodePudding

My Flutter app makes use of an SQLite database with multiple tables and Firebase authentication. Some time after publishing the app, I added a User ID column to each table, so that I could ensure that results could be filtered by users and they would only see their own data, should another user log into the app on the same device.

When I created the new column in each table though, for the entries that already existed, I allowed the data in the new column to be null to prevent an error with the database. The problem with that is that users who made the initial calculations where the User ID is null will now 'lose' that data, as it won't show up when I implement filtering with User ID.

What I want to do is bulk-update the null values in that column of each SQLite database table, if a null value exists in that table. The null values should be replaced by the current logged in User ID. I'm not sure of the best way to do this but my idea is something like this, with a database update function after it has been initialised:

Future _update(Database db, int oldVersion, int newVersion) async {
  
  if (oldVersion < newVersion) {

      Future<int> updateDB(String value) async {
        final db = await instance.database;
        return db.update(
          values,
          value.toJson(),
          where: '${ValueFields.id} = ?',
          whereArgs: [value.id],
        );
      }

  }

}

Thing is, this doesn't seem to actually work and of course doesn't bulk update all rows in that particular column. Can someone advise on how I can build the bulk update function, to take all null User ID values in a particular table and change them to the current logged in user?

Thank you!

CodePudding user response：

What you are doing is never updating anything if id is null. That is even if null were passed as the id via the whereargs, it would never update any rows as null is considered unique/distinct so will never be the same as another null. Instead you can use IS NULL. If the id is not null then it would not update rows where the id is null.

You can update all in one go if you use, (in SQL)

UPDATE the_table SET the_column = the_value WHERE the_column IS NULL

which I believe would be along the lines of:-

    return db.update(
      values,
      value.toJson(),
      where: '${ValueFields.id} IS NULL', /*<<<<< CHANGED WHERE CLAUSE */
      whereArgs: [], /*<<<<< CHANGED NO WHERE ARGS*/
    );