Thread
#exposed
    r

    runjorel

    3 years ago
    How can I use a sub query in an update statement ? Contrived Example:
    UPDATE Users SET active=false WHERE id IN (SELECT user_id FROM deactivated)
    It seems the only option I have is
    inList
    but I am afraid if I call
    map{..}/toList()
    on my subquery it would materialize those results.
    tapac

    tapac

    3 years ago
    It’s better to update it like this:
    Users.innerjoin(Deactivated).update {
        it[Users.active] = false 
    }
    But if you want to use subquery than:
    val subquery = wrapAsExpression<List<EntityId<Int>>(Deactivated.slice(Deactivated.userId).selectAll())
    Users.update( where = {
    Users.id inList subquery}) {
       it[Users.active] = false 
    }
    r

    runjorel

    3 years ago
    thank you!