asked on
Updating many rows separately
I need to update all the rows in a MySql table like this:
The problem is that there can be thousands of rows and it is very slow.
Is there a better way to do it?
Thank you!
START TRANSACTION;
update product set t1="t1" where p_id=40717;
update product set t1="3" where p_id=40713;
.....
commit;
The problem is that there can be thousands of rows and it is very slow.
Is there a better way to do it?
Thank you!
MySQL ServerSQL
Last Comment
Mlanda T
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Swatantra Bhargava: I did exactly the same thing but it is still very slow ... :-(
It will be - you're still running individual queries - one for each row. Simply concatenating them with a semi-colon is not likely to make much difference.
If you explain what rules you have for updating the rows, then maybe we can give you some better advice.
If you explain what rules you have for updating the rows, then maybe we can give you some better advice.
You may also get better performance by using a prepared statement, but we still need to know your rules.
You could generate the code to do all of them as part of one UPDATE statement, which should yield better performance, although how much better you won't know until you run it.
update product
set t1 = case p_id when 40717 then "t1" when 40713 then "3" when ... then ... end
where p_id IN (40717, 40713, ...);
update product
set t1 = case p_id when 40717 then "t1" when 40713 then "3" when ... then ... end
where p_id IN (40717, 40713, ...);
WITH updates(t1_filter,value) AS (
SELECT 't1', 40717
UNION SELECT '3', 40713
)
UPDATE product SET p_id = updates.value
FROM product
INNER JOIN updates ON updates.t1_filter = product.t1
UPDATE product SET t1='A' WHERE Id=1;UPDATE product SET t1='B' WHERE Id=2;UPDATE product SET t1='C' WHERE Id=3;
By this, you can make the single statement and execute that.