MySql - Updating Multiple Rows with 1 Query

[Lawrence]

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.

 

[Lawrence]

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

 

[SheepCow]

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

 

[Lawrence]

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.

 

[SheepCow]

I think you want ...

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

 

[Lawrence]

I think you want ...

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

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.

 

[xfrocks]

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

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