Update top n rows of a table in slick

Question

I want to update top n rows of a table not the entire one when using slick 3.0

This is the update all version:

private[this] val active = this.filter(a => a.status =!= AccountStatus.DISABLED)
db.run(
    active.filter(a => a.usedBy.isEmpty || a.usedBy === Host.name)
    .map(account => account.usedBy)
    .update("host-a")
)

I tried to use this version but it didn't work and thrown an exception

private[this] val active = this.filter(a => a.status =!= AccountStatus.DISABLED)
db.run(
    active.filter(a => a.usedBy.isEmpty || a.usedBy === Host.name)
    .take(10)
    .map(account => account.usedBy)
    .update(Option(Host.name))
)

Exception

Caused by: slick.SlickException: A query for an UPDATE statement must resolve to a comprehension with a single table -- Unsupported shape: Comprehension s2, Some(Apply Function and), None, ConstArray(), None, None, Some(LiteralNode 100 (volatileHint=false)), None
at slick.driver.JdbcStatementBuilderComponent$QueryBuilder.buildUpdate(JdbcStatementBuilderComponent.scala:447)
at slick.driver.JdbcProfile$$anonfun$updateCompiler$1.apply(JdbcProfile.scala:30)
at slick.driver.JdbcProfile$$anonfun$updateCompiler$1.apply(JdbcProfile.scala:30)
at slick.jdbc.JdbcMappingCompilerComponent$JdbcCodeGen.compileServerSideAndMapping(JdbcMappingCompilerComponent.scala:59)

Answer 1

Well... the answer to this lies in that fact that you are trying to do something which Slick is not supposed to do.

A very simple guideline - When in doubt think in SQL then transform to Slick

Just think how will you achieve this in SQL,

If I transform your "query",

// lets say Host.name = "Awesome-Host"

active.filter(a => a.usedBy.isEmpty || a.usedBy === Host.name)
  .take(10)
  .map(account => account.usedBy)
  .update(Option(Host.name))

to SQL, it will be something like this,

UPDATE
    active
SET
    used_by = 'Awesome-Host'
WHERE
    used_by IS NULL
    OR used_by = 'Awesome-Host'
LIMIT 10

Which is kind of absurd from an SQL perspective...

Now... Lets talk about how will you actually do it with SQL,

UPDATE
    (
        SELECT 
            *
        FROM
            active
        WHERE
            used_by IS NULL
            OR used_by = 'Awesome-Host'
        LIMIT 10
    ) active_selection
SET
    active_selection.used_by = 'Awesome-Host'

And... this can be translated to Slick using Sub-Queries

val activeSelection = active
  .filter(a => a.usedBy.isEmpty || a.usedBy === Host.name)
  .take(10)

val updateSelection = activeSelection
  .map(a => a.usedBy)
  .update(Option(Host.name))

Answer 2

We recently ran into a similar situation.

The solution was to use a subquery:

val subquery = myTable.filter(...).take(1)

myTable
  .filter(_.id in subquery.map(_.id))
  .map(row => ...)
  .update(...)