updating multiple rows at the same time

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
taldos
Forum Commoner
Posts: 39
Joined: Mon Aug 23, 2004 8:47 am
Location: Philadelphia

updating multiple rows at the same time

Post by taldos »

quick question.

is there a way to store variables in an array then update multiple rows at the same time. Here's the situation. I have a series of user who's ranking order I want to change. Now instea of looping through every users id and on each count performing an "update" call. is there a way to store new variables into an array than just pass the array to mysql to update.

I ask because I am sure that with growing sets of data, making an individual call to "update" for each user will slow down the connection. especially when there will be over 1000 users.

best,

Edward M.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

would these updates be setting the same-ish data? ... like setting group_id to 2, and copy field x to field y kinda thing..
taldos
Forum Commoner
Posts: 39
Joined: Mon Aug 23, 2004 8:47 am
Location: Philadelphia

Post by taldos »

Well it would not be updating the same exact same data to every row, that is simple enough. Example, I only want to update values within a field called "rank". however each row will have a different value for the "rank" field. I suppose there is no way around it especially if I have to first match the user id to his/her individual ranking.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the only real thing you can do, is pool the ranks together.. and use a "WHERE user_id IN(1,2,3,4,21)" kinda set up..
Post Reply