updating multiple rows at once

Posted on 2006-04-16
Last Modified: 2007-12-19

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.

Question by:gamebits
    LVL 30

    Accepted Solution

    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).
    LVL 27

    Expert Comment

    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.
    LVL 8

    Expert Comment

    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 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.

    LVL 10

    Expert Comment

    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.
    LVL 28

    Author Comment

    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.



    LVL 28

    Author Comment

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



    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (, A SQLite Tidbit: Quick Numbers Table Generation (…
    Both Easy and Powerful How easy is PHP? (  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now