I have an update query e.g.:
UPDATE table SET name = ? WHERE id = ?
I have all the relevant ids collected. I am looping over the ids and executing the update query for each one. In pseudo code:
for (id..ids) {
stmt->executeUpdate(query, nameForId(id), id);
}
I was wondering is there a smarter/more efficient way to do this query update without looping? For instance, is there a way to create a big string once, like:
UPDATE table set a=? where id=? AND a=? where id=? ...etc.
Best Answer
Philᵀᴹ: Insert all of the data pairs into a temporary staging table, then run a single update. Will be lots quicker.
Kondybas: Create table
pairs(id,name)
from collected data and then:Wiki answer generated from comments on the question.