normally I can create a SQL query like this to update two separate tables:
UPDATE Books, Orders
SET Orders.Quantity = Orders.Quantity + 2,
Books.InStock = Books.InStock - 2
WHERE
Books.id = Orders.BookID
AND Orders.id = 1002;
DAO would be like:
internal object Books : LongIdTable() {
val InStock: Column<Long> = long("in_stock")
}
internal object Books : LongIdTable() {
val Quantity: Column<Long> = long("quantity")
val BookID: Column<Long> = long("book_id").references(Books.id)
}
What's the recommended way to perform similar SQL query using Exposed?
2) Separate question, can we write two update queries in a single transaction block? Something like this:
transaction {
TableA.update({ TableA.id eq id }) { row ->
row[TableA.status] = appStatus
}
TableB.update({ TableB.appID eq id }) { row ->
row[TableB.status] = userStatus
}
}
Thanks for any pointers.