CODEWITHSUNDEEP
scalaslick
ais
ais

Reputation: 2624

How to update column with another subquery value in Slick?

I want to do something like this

UPDATE item
SET value = ( 
  SELECT max(value)
  FROM item
) 
WHERE id = 1;

I tried

for {
  maxValue <- Tables.Item.map(_.value).max
  x <- Tables.Item
    .filter(item => item.id === 1)
    .map(_.value).update(maxValue)
} yield x

but maxValue is a Rep[Int] instead of Int

Upvotes: 0

Views: 715

Answers (1)

Richard Dallaway
Richard Dallaway

Reputation: 4320

Slick's update doesn't support dynamic values or sub-queries. You have a couple of options for this situation.

First, you can use Plain SQL:

sqlu""" UPDATE item SET value = (SELECT max(value) FROM item) WHERE id = 1 """

Second, you could run the expression as two queries (potentially inside a transaction). This is similar to the example you have as update is a DBIO, rather than a Query.

I'd expect max to have an optional value as there might be no rows in the table:

val updateAction: DBIO[Int] = 
   Tables.Item.map(_.value).max.result.flatMap {

       case Some(maxValue) => 
        Tables.Item
          .filter(item => item.id === 1)
          .map(_.value).update(maxValue)

       case None => 
         DBIO.successful(0) // ... or whatever behaviour you want
}

However, perhaps your value field is already an option and you can use your existing for comprehension with the addition of .result on the end of the maxValue expression as mentioned by @Duelist.

Upvotes: 2

Related Questions