CODEWITHSUNDEEP
scalaslickslick-3.0
ps0604
ps0604

Reputation: 1081

Updating multiple rows in a single transaction in Slick 3.1.x

Assume a table table1 has three columns col1, col2 and col3. Also, assume I need to update col1 based on the value of col2. For example, with the following SQL statement

update table1 set col1=111 where col2=222

Let's also say I have to update 1000 times table1 and I have the information in the following Seq:

case class Table1 (col1: Int, col2: Int, col3: Int)
val list = Seq(Table1(111,222,333),Table1(111,333,444), .... )

What's the best way to update 1000 rows in Slick 3.1.x? Is it possible to run a batch statement with foreach?

 val action = table1.foreach(....)

Upvotes: 1

Views: 5255

Answers (1)

Tyler
Tyler

Reputation: 18187

You can use DBIO.sequence to build a list of actions to execute, something like this:

db.run(DBIO.sequence(list.map(l => ???)))

Here is a more complete example:

import scala.concurrent.Await
import scala.concurrent.duration.Duration
import slick.driver.H2Driver.api._


object main {

    // The class and corresponding table
    case class Thing (id: String, col1: Int, col2: Int)
    class Things(tag: Tag) extends Table[Thing](tag, "things") {
        def id = column[String]("id", O.PrimaryKey)
        def col1 = column[Int]("col1")
        def col2 = column[Int]("col2")

        def * = (id, col1, col2) <> ((Thing.apply _).tupled, Thing.unapply)
    }

    val things = TableQuery[Things]


    def main(args: Array[String]) {

        val db = Database.forConfig("h2mem1")

        try {

            // Create schema
            Await.result(db.run(things.schema.create), Duration.Inf)

            // Insert some things for testing
            Await.result(db.run(DBIO.seq(
                things += Thing("id4", 111, 111),
                things += Thing("id5", 222, 222),
                things += Thing("id6", 333, 333)
            )), Duration.Inf)

            // List of things to update
            val list = Seq(Thing("id1", 111, 112), Thing("id2", 222, 223), Thing("id3", 333, 334))

            // ----- The part you care about is here -----
            // Create a list of Actions to update
            val actions = DBIO.sequence(list.map(current => {

                // Whatever it is you want to do here
                things.filter(_.col1 === current.col1).update(current)
            }))

            // Run the actions
            Await.result(db.run(actions), Duration.Inf).value

            // Print out the results
            val results = Await.result(db.run(things.result), Duration.Inf)
            println(results)


        } 
        finally db.close
    }
}

The output has the updated col2 values:

Vector(Thing(id1,111,112), Thing(id2,222,223), Thing(id3,333,334))

Upvotes: 11

Related Questions