If you may need to update multiple rows of data in a table. This
is no problem a lot of the time, as the fields to update may all need to
be changed to the same value, in which case you might run a query like
the following.
UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value';
But what about if you want to update multiple rows but you need the
field set to a different value for each row? For example, maybe you have
a table of categories with a field to store their display order. How
would you update the display order field for each category when the
order changes? Most often you will see people just run multiple queries.
For example, if you are using PHP you might think to do something like
this:
foreach ($display_order as $id => $ordinal) {
$sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id";
mysql_query($sql);
}
Of course this will work fine, but queries in loops make me start to
sweat nervously. It’s all to easy to start hammering the database with a
crazy number of queries. Fortunately there is a better way! The SQL is a
little more complex, but it is possible to achieve the same result
using only one query. The syntax is as follows.
UPDATE mytable
SET myfield = CASE other_field
WHEN 1 THEN 'value'
WHEN 2 THEN 'value'
WHEN 3 THEN 'value'
END
WHERE id IN (1,2,3)
For multiple Fields use below sql query .
UPDATE categories
SET display_order = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END,
title = CASE id
WHEN 1 THEN 'New Title 1'
WHEN 2 THEN 'New Title 2'
WHEN 3 THEN 'New Title 3'
END
WHERE id IN (1,2,3)
