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!
Member_2_7965240Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris StanyonWebDevCommented:
There's not really enough to go on there. If you want each row to be updated to a different value, then yes .. you'll have to run a query for each row. However, if you want to update a given batch of records to the same value, then you can probably batch them up. Something like this:

UPDATE product SET t1="3" WHERE p_id IN (40713, 40714, 40715, 40716, 40717);

or this:

UPDATE product SET t1="3" WHERE p_id >= 40713 AND p_id <= 40800;
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Swatantra BhargavaTechnical Specialist/DeveloperCommented:
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.
0
Member_2_7965240Author Commented:
Swatantra Bhargava: I did exactly the same thing but it is still very slow ... :-(
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Chris StanyonWebDevCommented:
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.
0
Chris StanyonWebDevCommented:
You may also get better performance by using a prepared statement, but we still need to know your rules.
0
Scott PletcherSenior DBACommented:
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, ...);
0
MlandaTCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.