How to update column with another subquery value in Slick?

Question

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

As far as I remember you can call result method: Tables.Item.map(_.value).max.result — Duelist
@Duelist max.result is a DBIO so I believe it will create two separate database queries instead of single SQL statement — ais

Richard Dallaway Richard Dallaway · Accepted Answer · 2020-05-13T15:07:27

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.

How to update column with another subquery value in Slick?

1 Answers