updating multiple rows at once


I query the db and I get a number of rows returned.

They are displayed in a form with fields to be updated

I know how to do the update on a one by one basis.

What I want to do is be able to update multiple rows at once.

Let say for example 5 fields per row and 10 rows.

There is something about arrays and loop thru it that I cannot get to work.

Any help would be greatly appreciated.

LVL 28
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

UPDATE mytable SET user_state='CA' WHERE zipcode between 92000 AND 95000;

would udate all rows in the table that match the criteria.  If you are wanting to update records to different values, you need to do this at the application layer (or possibly in a stored procedure with MySQL 5.0).

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
Artysystem administratorCommented:
I aggree with todd_farmer, but have some more explaination.

'I query the db and I get a number of rows returned' - it's your starting point.
You already have some query statement like: SELECT * FROM lala WHERE lalala;
Just change 'SELECT * FROM lala' to 'UPDATE lala', then append before WHERE: 'SET col1=val1, col2=val2, ... '
and you will get SQL statement, that you asked for.
In addition to todd_farmer's and Nopius' comments, you can also update according to results of a subquery:
update t1 set t='zzzz' where id in (select id from t2)

this query for example, will update t column in t1 table where the t1.id is found in the subquery (select id from t2).
every row that has an id in t1 which is equal to any id in t2 will be updated.

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

When I want to update records to different values, I usally have a table that holds the update criteria & values and combine the two tables with join.
As an example I will use the same example with todd.

I would have a table (matchtable) that would hold zipcodes/states.

left join match_table on(matchtable.zipcodemax > mytable.zipcode && matchtable.zipcodemin < mytable.zipcode)
SET mytable.user_state=match_table.stat
WHERE match_table.state IS NOT NULL;

Of course this approach doesnt serve all cases, but it is quite good, when u want to avoid modifyinh your application layer or you are not using mysql 5 or later.
gamebitsAuthor Commented:
I think I need to clarify my question, I'll go with a example, but I think Todd might be right it's more at the application level (php) than at the mysql level that I will find the answer, anyway here it goes.

1) I query the db which return 5 rows

checkbox(id1), field1, field2, field 3, field4, field5
checkbox(id2), field1, field2, field 3, field4, field5
checkbox(id3), field1, field2, field 3, field4, field5
checkbox(id4), field1, field2, field 3, field4, field5
checkbox(id5), field1, field2, field 3, field4, field5

as you can see I have checkbox setup so I can choose which record to update and query the db again to populate the update form. Lets make it simple I will choose id 1 and 3 so the next page I have the form displayed twice populated with the appropriate data according to the id.

field1: input box with data1
field2: input box with data2
field3: input box with data3
field4: input box with data4
field5: input box with data5

field1: input box with data1
field2: input box with data2
field3: input box with data3
field4: input box with data4
field5: input box with data5

button submit value=edit

Now I'm able to edit the data in the forms and it the submit button. If there was only one form to update one row I wouldn't have any problem but since I have 2 forms to edit/update 2 rows I hust cannot figure it out.

And my example has only 5 fields and i want to update 2 rows but in real life there is about 160 ids and up to 14 fields to edit for each one of them.



gamebitsAuthor Commented:
Thanks guys but Todd was right, for the query to update multiple rows with different value it had to be done at the application level, (I do not run mysql 5.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
MySQL Server

From novice to tech pro — start learning today.