CODEWITHSUNDEEP
sqlscalaslickupsert
preston.m.price
preston.m.price

Reputation: 666

Can Slick's insertOrUpdate modify a subset of columns in the event the record already exists?

In my use case I have a createdDate field that I would like to preserve in the event that the record already exists.

case class Record(id:Long, value:String, createdDate:DateTime, updateDate:DateTime)

Is it possible to use a TableQuery.insertOrUpdate(record) such that only parts of the record are updated in the event the record already exists?

In my case I'd want only the value and updateDate fields to change. Using plain SQL in a stored procedure I'd do something like:

merge Record r
using (
    select @id,
           @value
) as source (
    id,
    value
)
on r.id = source.id
when matched then 
    update set value = source.value, updateDate = getDate()
when not matched then 
    insert (id, value, createdDate, updatedDate) values
    (id, value, getDate(), getDate()

Upvotes: 3

Views: 561

Answers (1)

Jesse Webb
Jesse Webb

Reputation: 45303

Can Slick's insertOrUpdate modify a subset of columns?

No, I don't believe this is possible with the insertOrUpdate function. This has been requested as a feature but it is not currently implemented.

How can we work around this?

Since the update function does support updating a specific list of columns, we can write our own upsert logic instead of using the insertOrUpdate function. It might work like this:

def insertOrUpdate(record: Record): Future[Int] = {
  val insertOrUpdateAction = for {
    recordOpt <- records.filter(_.id === record.id).result.headOption
    updateAction = recordOpt.map(_ => updateRecord(record))
    action <- updateAction.getOrElse(insertRecord(record))
  } yield action
  connection.run(insertOrUpdateAction)
}

private def updateRecord(record: Record) = {
  val query = for {
    r <- records.filter(_.id === record.id)
  } yield (r.value, r.updatedDate) // list of columns which can be updated
  query.update(record.value, record.updatedDate)
}

private def insertRecord(record: Record) = records += record

Upvotes: 1

Related Questions