Update Multiple Rows With Different Values and a Single SQL Query
Sometimes 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.
1 | 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:
1 2 3 4 | 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.
1 2 3 4 5 6 7 | 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) |
Mapping this to the categories example, we get the following query.
1 2 3 4 5 6 7 | UPDATE categories SET display_order = CASE id WHEN 1 THEN 3 WHEN 2 THEN 4 WHEN 3 THEN 5 END WHERE id IN (1,2,3) |
This is fairly simple to understand. Rather than setting a field to a particular value, a CASE operator is used to determine which of a set of values is used based on a given condition, in this case if the value of the id field matches the specified id.
The WHERE clause is not needed as such in that it doesn’t affect the logic of the query, but it does improve performance by ensuring that the logic is only applied to the smallest number of rows possible. In this example only 3 rows are being updated, and the WHERE clause ensures that only those 3 rows are tested. Without it the every row in the table would be tested (unnecessarily, since they will never match).
What about when you need to update multiple fields? This is easily done just by adding another CASE block.
1 2 3 4 5 6 7 8 9 10 11 12 | 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) |
Now this is all well and good, but the real beauty of this comes when the technique is combined with a scripting language such as PHP in order to build these queries dynamically. Let’s examine one technique for doing this, using the category ordering example.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | // An array containing the category ids as keys and the new positions as values $display_order = array( 1 => 4, 2 => 1, 3 => 2, 4 => 3, 5 => 9, 6 => 5, 7 => 8, 8 => 9 ); $ids = implode(',', array_keys($display_order)); $sql = "UPDATE categories SET display_order = CASE id "; foreach ($display_order as $id => $ordinal) { $sql .= sprintf("WHEN %d THEN %d ", $id, $ordinal); } $sql .= "END WHERE id IN ($ids)"; echo $sql; |
Now in this particular example, only 8 rows are being updated, but 7 queries have been trimed, which is not insignificant. Apply this technique to a situation where hundreds or thousands of rows have to be updated and you can imagine the benefits it will have.
Hi Karl
Thanks for this very valuble sample. I have used loops with all the overheads and this has caused real problems. This has cured them all. Many Thanks.
Thanks to your post I could figure out another way to do such stuff in pure MySQL:
UPDATE
table_containing_values_you_want_to_change as t,
JOIN (
/* select statement resulting in a table
containing the id to identify the row you want to change and the values you want to be set to */
SELECT
id,
newValue1,
newValue2, …
FROM
whatever
WHERE
whatever
) as newValues
SET
t.v1 = newValues.newValue1,
t.v2 = newValues.newValue2, …
WHERE
t.id = newValue.id#
!
Thank you very much for this great little tip!!
Pingback: 10 sql tips to speed up your database | Revolução21
I must commend u, great job…
Hi!
Thanks Karl! This was a new thing for me.
I was doing some cron work and the data size was really large that would have caused hundreds of extra queries.
One thing I want to point your attention to: Isnt the “WHERE id IN (1,2,3)” part of the query redundant? When you have already specified conditions in CASE ‘WHEN’ then I think you do not need to tell it again to limit the updated to the rows where id IN (STRINGs).
I have tested it, and it works without the part that I mentioned as redundant, but I still need your opinion because I am not much confident writing innovative SQL myself.
Thanks once again.
Hi Hamid
You are correct in that you don’t need the WHERE clause for the query to run correctly. The reason for it though is efficiency. Using the WHERE clause limits the number of rows which must be tested in the CASE statement. Without this the query would result in every row of the table being tested, which for large tables could cause noticeable slowdown.
Thanks Karl,
this was really helpful.
Can you please provide (if possible) benchmark results (on this post, or on new post somewhere on your blog) for above queries with and without ‘WHERE’ on a table that has more than a million Rows and you use up to 20 conditions in CASE syntax.
Thanks again.
Pingback: [MySQL] Multiple-Row-Update - Delphi-PRAXiS
Karl,
I am using your query in my QTP automation test. I wonder if there are more than one condition, say Case id1, id2, …, how should I change the query?
cool Stuff Karl!
Many thanks for this! This helped me solve my problem
Hi Karl,
Thanks a lot for this bit of SQL snippet. It’s really awesome using it. However, when I read the earlier comments I’m not too sure how one would go about avoiding the 255 CASE limit that MySQL imposes. There could be a case where you need to update more than 255 rows in which case this could potentially fail. Is there any easier way as opposed to creating joins of two tables etc. Can nesting CASE expressions be helpful?
Thanks man for the great post, I really needed this because I have thousands of rows in my table.
how to updated values in a table by using the id of the values in some other tables….
this to update multiple column with one sql query
UPDATE dest_table t1
SET
field1 = (select field1 from src_table t2 where t2.pk = t1.pk),
field2 = (select field2 from src_table t2 where t2.pk = t1.pk),
…etc.
WHERE EXISTS (select 1 from src_table t2 where t2.pk = t1.pk)
UPDATE testing
SET newrsuamount =
CASE
WHEN @count = 1 THEN RSUAmount+ @RSUAmount
ELSE RSUAmount + RSUAmountdiff
END,
@count = @count – 1,
@RSUAmount = @RSUAmount – RSUAmountdiff
what will happens the above statment and what is the qquivalent statement in SQL server . any idea what is the name for this upda. I tried running the above statment in sql server and sybase which is give worng values for the @count=1 , i want the same out put as returning like sybase.could you please help me any body
Thanks, it was very helpful!
Hi Karl
I want to update following
col1 col2 col3
1 abc
2 abc
3 pqr
to
col1 col2 col3
1 abc 1
2 abc 1
3 pqr 3
plzz help
am kinda a newbie to php and mysql…
how would u update multi rows tht has more than one field
Pingback: Update Multiple Rows with Unique Values – MySQL « Intellega Tech Blog
Good……….. Thanks a lot.
Thank you..!
thank you its very useful for me and my friends
This was the most amazing tip on sql ever!!
I was able to solve a 2963 + 1520 duplicated rows (from 2 different dbs) with a single query….. obviously i made a table_copy and use the same method to achieve the goal.
UPDATE characters AS t, (SELECT `guid`,`name` FROM `characters_copy` GROUP BY `name` HAVING COUNT(`name`)=2) AS n SET t.`name`=CONCAT(t.`name`,’aaa’) WHERE t.guid=n.guid;
Thank you so much!
it should be like
UPDATE empsal SET sal =
CASE WHEN sal BETWEEN 100 AND 500 THEN sal + 5000
WHEN sal BETWEEN 40000 AND 55000 THEN sal + 7000
else sal
END
Pingback: Efficient Update SQL, updating multiple rows with one SQL statement, Avoiding Loops | DIGG LINK
Good one sir this is what i’m looking for thanks! sir is it possible to use column increments like display_order=display_order+1 based on your example, because in my case im trying to update the quantity of the inventory based on their status. like for example i updated a certain item to status ‘InStock’ i would also like my table inventory column ‘InStock’ value to increment . i would appreciate it very much if you email me regarding this question. thank you.