How to update all rows in a specific column in the Room database

I have a Room database in Android Studio, 100k fixed rows, 10 columns .. just using them to perform calculations.

After performing some calcs on each row I add a value to a list. I want to update the last column in the database with this value for each row. But I don't want to send the update query each step through the list or it will take forever.

I have tried to pass in 2 lists, one for the id and one for the calculated value, into the statement but it crashes the app. Passing just the id list in works fine but not the combo list. I am not a trained programmer by any stretch, just a hobby but if someone could point me in the right direction I would appreciate it.

    @Query("UPDATE mytable SET combin = (:combo) where ID in (:id)")      fun updateCombinations(id: List<Int>, combo: List<Long>)

Using JDBC in Intelliji I was able update all 100k rows with a prepared statement and batching like so ..

    var conn: Connection? = null      var pst: PreparedStatement? = null        try {          conn = connect()          pst = conn!!.prepareStatement("UPDATE mytable SET combin = ? WHERE ROWID = ?")            conn.autoCommit = false            val batchSize = 1000          var count = 0            combin().forEachIndexed { i, n ->                pst.setLong(1, n)              pst.setInt(2, i + 1)              pst.addBatch()                if(count++ % batchSize == 0) {                  pst.executeBatch()              }          }          pst.executeBatch()          conn.commit()

https://stackoverflow.com/questions/66620429/how-to-update-all-rows-in-a-specific-column-in-the-room-database March 14, 2021 at 10:06AM