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