3

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.

Last61474
  • 43
  • 3

1 Answers1

0

Yes, you can write more than one statement per transaction block, and I think it is a reasonable way to implement your update

Rich
  • 11
  • 2