Search code examples
squeryl

Squeryl update from select?

Given the following widgets table

╔════╦═════════╦═════╗
║ id ║ prev_id ║ foo ║
╠════╬═════════╬═════╣
║  1 ║         ║ bar ║
║  2 ║       1 ║     ║
╚════╩═════════╩═════╝

And the following sql query

UPDATE widgets 
SET 
  widgets.foo = 
    (
     SELECT widgets.foo
     FROM widgets 
     WHERE widgets.id = 1
    )
WHERE
    widgets.id = 2

How do I do the above update in squeryl?

I tried 

  update(widgets) (
    w=>
      where(w.id === 2)
        set(w.foo := from(widgets)(prevW => where(prevW.id === 1) select foo))
  )

But that gave me the following compile error:

error: No implicit view available from org.squeryl.Query[Option[String]] => org.squeryl.dsl.ast.TypedExpressionNode[Option[org.squeryl.PrimitiveTypeMode.StringType]].
Solution

  • It looks like Squeryl added support for sub queries with https://github.com/max-l/Squeryl/commit/e75ddecf4a0855771dd569b4c4df4e23fde2133e

    However it needs an aggregate query that is guaranteed to return one result. This is done with the compute clause.

    I came up with a workaround using compute(min(foo)).

    So my solution ends up looking like

    update(widgets) (
  w=>
    where(w.id === 2)
    set(w.foo := from(widgets)(prevW => where(prevW.id === 1) compute(min(foo))))
)

    Maybe there should be a single aggregate function that throws an exception if the query returns more than one result.

    Related conversations: