Search code examples
swiftsqlitegrdb

Updating multiple rows using instr


I am trying to make the following call:

UPDATE MyTable SET path = ? WHERE instr(title, ?) AND start - ? < 60

However I have not been able to use instr with GRDB.

_ = try dbQueue?.write { db in
    try MyTable
        .filter(Column("start") > date - 60)
        .filter(title.contains(Column("title")))
        .updateAll(db,
                   Column("path").set(to: path)
        )
}

How can I do this correctly? Could I also run a raw query instead? How can I fill the ? with my variables if using a raw query?


Solution

  • GRDB does not ship with built-in support for the instr function. You can define it in your code:

    func instr(_ lhs: some SQLExpressible, rhs: some SQLExpressible) -> SQLExpression {
        SQL("INSTR(\(lhs), \(rhs))").sqlExpression
    }
    
    // SELECT * FROM myTable WHERE instr(?, title)
    let title: String = ...
    let request = MyTable.filter(instr(title, Column("title")))
    
    // UPDATE myTable SET path = ? WHERE instr(?, title)
    let path: String = ...
    try request.updateAll(db, Column("path").set(to: path))
    

    See the How do I print a request as SQL? faq in order to control the SQL generated by GRDB.