1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySql - Updating Multiple Rows with 1 Query

Discussion in 'General PHP and MySQL Discussions' started by Lawrence, Jul 1, 2011.

  1. Lawrence

    Lawrence Well-Known Member

    MySql's UPDATE does not support updating multiple rows with one query. As far as I know there are 2 ways to do so:

    INSERT INTO... ON DUPLICATE KEY UPDATE, and using CASE... WHEN...

    I'm leaning on using the INSERT/UPDATE as the class will only be called when there is an existing id (or ids) already in the table. Is there a reason I should not use INSERT/UPDATE to update specific fields for multiple rows at one time? Is there an advantage using the CASE WHEN method, instead (ie: faster execution)?

    Thanks.
     
  2. Lawrence

    Lawrence Well-Known Member

    After a lot of testing I decided on CASE WHEN, WHERE... IN. Works well as the correct id's are being updated with the correct data for that id, and seems pretty quick (thanks to the WHERE IN statement, I believe, as MySql is limited to those ids).
     
  3. SheepCow

    SheepCow Well-Known Member

    Erm, I may not be understanding you properly, but

    UPDATE tablename SET field1="Yes" WHERE id IN (1,2,6,8,3,153)
    UPDATE tablename SET field1="Yes" WHERE id < 5
    UPDATE tablename SET field1="Yes" -- sets field1 to yes for all rows in the table
     
  4. Lawrence

    Lawrence Well-Known Member

    Each id passed to the model contained values that are not the same as the values of other id's, this value then needed to be added to the existing value associated with that id. For example:

    id's: 1,3,8, values: 10,45,10 and then..
    id's 3,10,15,27, values: 45,8,90,60

    As far as I know MySql can not do this with one query without using the insert/update on duplicate key, or the case... when statements.
     
  5. SheepCow

    SheepCow Well-Known Member

    I think you want ...

    REPLACE INTO tablename (id, values) (3, 45) (10, 8) (15, 90) (27, 60)
     
  6. Lawrence

    Lawrence Well-Known Member

    I need to add the values to existing values, and from my research there is only two ways to update a specific field for numerous rows with one query. The case... update performs this well, but I'm not sure if it is more efficient than the insert/update.
     
  7. xfrocks

    xfrocks Well-Known Member

    That's the way to go. I learnt this reading vBulletin code so I guess its performance is good enough :D
     

Share This Page