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.

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:

UPDATE table 
JOIN pairs ON table.id=pairs.id
SET table.name=pairs.name

Wiki answer generated from comments on the question.


Yes - you can concatenate your id's into a comma delimited string and then issue the following command:

update table set a=? where id in (?,?,?,?)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.