hey guys, can not find how to implement batch update in exposed, please help! I am using DSL

# exposed

[Timur Atakishiev]

hey guys, can not find how to implement batch update in exposed, please help! I am using DSL

[tapac]

val batchUpdate = BatchUpdateStatement(FooTable)
dataToUpdate.forEach { entry ->
   batchUpdate.addBatch(entry.id)
   batchUpdate[FooTable.bar] = entry.bar
}
batchUpdate.execute(TransactionManager.current())

[Timur Atakishiev]

Thank you very much

[Mourad]

@tapac How would one do a batch update with more than one condition in the where clause?

I see that BatchUpdateStatement extends from UpdateStatement but does not allow to override the where section... Perhaps this is done because the id of that element is been put there (obviously) to know which element to update. Is there a way to extend this where-clause with more conditions?

[tapac]

Could you explain what the batch update with where condition means?

[Mourad]

Perhaps an example would explain this a bit more. So lets say that with the code you mentioned above the following sql will be generated:

UPDATE Items SET columnName='value' WHERE id = '2e9a4830-33b3-44f9-99af-98b7b5472c22'

The BatchUpdateStatement does not allow to override the where-clause but generates the where-clause by using:

${transaction.identity(table.id)} = ?

Is there a way to extend the where-clause to something like:

WHERE id = '2e9a4830-33b3-44f9-99af-98b7b5472c22' AND organizationId = 1

[Mourad]

@tapac I could also solve this by checking whether the items within that organization exists before triggering this batch update. Just wondering whether I could solve this in a single operation.

[tapac]

Do you want to set the same value for every id ?

[Mourad]

@tapac Yes, I do (if you are talking about the organization field)

[tapac]

Why not to make a single update then?

Items.update({
   (id eq '123' and orgId eq '1212') or 
   (id eq '1234' and orgId eq '12152')
}) {
 it[value] = '123'
}

I'm not sure what will be faster

[Mourad]

Ah that is what you meant by asking whether I wanted to set the same values for each item. Unfortunately, no… this is not the case, so per item different fields could’ve changed