Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql Bulk Update

I have to execute ~6k update queries on a table through sql (No Hibernate/JDBC). Query is something like

UPDATE A 
SET 
    some_id = 'value1'
WHERE
    id = 'value2';

It takes too long to execute all these queries. Is there a way to improve the performance?

like image 562
java_geek Avatar asked May 23 '13 08:05

java_geek


1 Answers

Create a temp table (containing just the value1 and value2 values) and populate it in bulk (ie, you can potentially do this with a single insert statement). Then do an update using a join between your existing table and the temp 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;    
like image 124
Kickstart Avatar answered Nov 03 '22 01:11

Kickstart