Batch update with in clause

Question

Is it possible to execute a batch update with a variable length in-clause against an sqlite db?

val data = Seq(
  Seq(Set(1,2,3), 50),
  Seq(Set(4,5,6,7), 51)
)

NamedDB(symbol).localTx { implicit s: DBSession =>
  sql"""
    update table_a
    set xs=(
      select group_concat(x) from (
        select distinct x from table_b where id in (?)
      )
    ) where id=?
  """.batch(xs.map(_.data): _*).apply()
}

This default approach causes scalikejdbc to log that the parameter is being set as an object (a warning?) and the result is that no updates are applied.

I tried using a parameter binder, but it is not possible to set the in (?) parameter as an array type as sqlite (and its JDBC driver) do not support arrays.

The alternative is to alter the SQL text to have a ? per value in the clause. This is not possible with a batch update as different rows have different quantities of values.

score 0 · Answer 1 · answered Jul 01 '18 at 13:00

My work-around is to group data by qty of in clause params and execute a separate batch update for each.

xs.map(_.data).groupBy(_.size - 1).foreach { case (length, data) =>
  NamedDB(symbol).localTx { implicit s: DBSession =>
    val inClauseParams = ("?" * length).mkString(",")
    SQL(
      s"""
       update table_a set xs=(
         select group_concat(x) from (
           select distinct x from table_b where id in ($inClauseParams)
         )
       ) where id=?
     """
    ).batch(data: _*).apply()
  }
}

Batch update with in clause

1 Answers1