Update Multiple Rows With Different Values and a Single SQL Query

Posted in SQL by Karl on

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.

77 comments

  • Joe Bohen

    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.

  • Hoffmann

    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#

    !

  • Justin

    Thank you very much for this great little tip!!

  • Pingback: 10 sql tips to speed up your database | Revolução21

  • Kassiny

    I must commend u, great job…

  • Hamid Sarfraz

    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.

  • Karl

    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.

  • Hamid Sarfraz

    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

  • Jin

    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?

  • Kamran

    cool Stuff Karl!

    Many thanks for this! This helped me solve my problem :)

  • Nicholas

    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?

  • Peter

    Thanks man for the great post, I really needed this because I have thousands of rows in my table.

  • tom

    how to updated values in a table by using the id of the values in some other tables….

  • Madhvi

    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)

  • suba

    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

  • Tarique Islam

    Thanks, it was very helpful!

  • nvamsk

    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

  • jermaine

    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

  • Habib

    Good……….. Thanks a lot.

  • rajnikant

    Thank you..!

  • premkumar

    thank you its very useful for me and my friends

  • Eilo

    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!

  • Pradeep Kumar Sharma (http://www.techtt.org)

    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

  • Jocsism

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>