Link to home
Start Free TrialLog in
Avatar of Member_2_7965240
Member_2_7965240

asked on

Updating many rows separately

I need to update all the rows in a MySql table like this:

START TRANSACTION;
update product set  t1="t1" where p_id=40717;
update product set  t1="3" where p_id=40713;
.....
commit;

Open in new window


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!
ASKER CERTIFIED SOLUTION
Avatar of Chris Stanyon
Chris Stanyon
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Make a Single Query with all update statements (concat) like below through loop in any scripting language like PHP

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.
Avatar of Member_2_7965240
Member_2_7965240

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.
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, ...);
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

Open in new window

I have used a WITH statement here, but you could write your updates to another table and use a similar syntax for the UPDATE statement (Update one table based on data that is in another table). This will be quicker than individual update statements.