Mysql bulk update

Question

I need to execute ~ 6k update requests in a table via sql (No Hibernate / JDBC). The request is something like

update Set some_id = 'value1' where id = 'value2'

All of these queries take too long to complete. Is there a way to improve performance?

Answer 1

Create a temporary table (containing only the values ​​value1 and value2) and fill it in bulk (i.e. you can do this with a single insert statement). Then upgrade using the connection between the existing table and the temporary table.

Something like

INSERT INTO SomeTempTable(id, some_id) VALUES (1,2), (3,4), (5,6), ....... UPDATE A INNER JOIN SomeTempTable ON A.id = SomeTempTable.id SET A.some_id = SomeTempTable.some_id;