Updating multiple rows in 1 column in MySQL

Question

What is the correct query to update multiple rows in MySQL at the same time?

I update only 1 column:

UPDATE example_table SET variable1 = 12 WHERE id=1; UPDATE example_table SET variable1 = 42 WHERE id=2; UPDATE example_table SET variable1 = 32 WHERE id=3; UPDATE example_table SET variable1 = 51 WHERE id=4; 

It seems like this might be inefficient, or if this is the most efficient request, let me know :)

Answer 1

you can use cases like below:

 UPDATE example_table SET variable1 = CASE id WHEN 1 THEN 12 WHEN 2 THEN 42 WHEN 3 THEN 32 WHEN 4 THEN 51 END WHERE id BETWEEN 1 AND 4 

Answer 2

Not applicable to your example, but you will probably find this useful:

 UPDATE table SET value = <value> WHERE field = <specific value> 

This way you can update one field in a table based on another field in the same table. All relevant lines will be updated. To give an example that I used at work this morning

 UPDATE porderitems SET currency = -2 WHERE ord = 40396 

This request updates the porderitems table (purchase order lines), setting the currency to -2 for all lines associated with purchase order 40396. The request does not know and does not care about how many lines are in this purchase order; they will all be updated.

Answer 3

if your values ​​are taken from another table:

 UPDATE example_table JOIN values_table ON values_table.id = example_table.id SET example_table.variable1 = values_table.value 

Answer 4

UPDATE personal_details SET country_id = 6, where the identifier is from 26 to 40. I think this code will work if the new value is the same and needs to be updated in several lines.