Flutter & Dart SQL Lite package: Is it possible to execute multiple Update statements in ONE query (different whereArg each time)

Question

I'm thinking about making my SQL query more efficient. One query statement instead of using a for-loop and spamming the DB with the same query and different values. Take a look at what I'm doing here and please check if it's possible to make this more efficient. From what I understand, you can have multiple values in a map that have the same value, but you cannot have more than one key that is the same. Hence my dilemma.

Future<void> updateAllGoalsFollowingReorder(
      List<Goal> newGoalTileList) async {
    final db = await _getDatabase();

    for (var goal in newGoalTileList) {
      var response = await db.update(
          _goalsTable,
          {
            'goalIndexForDb': goal.goalIndexForDb,
          },
          where: 'id = ?',
          whereArgs: [goal.id]);
      print(response);
    }
  }

I've tried creating a list of goal ids to place in the whereArgs. But for the map: { 'goalIndexForDb': goal.goalIndexForDb, }, I obviously cannot have the same key for each value. Is there a way of achieving this?

Answer 1

You can use batch update to do this type of work. Something like this code,

Future<void> updateAllGoalsFollowingReorder(List<Goal> newGoalTileList) async {
    final db = await _getDatabase();
    var batch = db.batch();

    for (var goal in newGoalTileList) {
        batch.update(
            _goalsTable,
            {'goalIndexForDb': goal.goalIndexForDb},
            where: 'id = ?',
            whereArgs: [goal.id]
        );
    }

    var responses = await batch.commit();
    print(responses);
}

reference:

https://pub.dev/packages/sqflite#batch-support