Update multiple rows in 1 column in MySQL

Question

What is the proper query for updating multiple rows in MySQL at the same time?

I am only updating 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;

This seems like it may be inefficient, or if it is the most efficient query 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 probably will find this useful:

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

This way you can update one field in a table on the basis of another field in the same table. All the applicable rows will be updated. To quote an example which I used at work earlier this morning

UPDATE porderitems
SET currency = -2
WHERE ord = 40396

This query update the porderitems table (purchase order lines), setting the currency to -2 for all the lines connected to purchase order 40396. The query neither knows nor cares how many lines there are in that purchase order; all of them will be updated.

Answer 3

UPDATE personal_details SET country_id= 6 where id between 26 and 40. I think this code would work if the new value is same and it needs to update in multiple rows.

Answer 4

if your values are from another table:

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