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

  • Monique Database

    This led me to believe I might be able to use a single update statement to increment a value in a table. Monique Database

  • Josh

    Greetings!

    Great post. However, my query is showing errors when I try to run it. Can you post what the final query is supposed to look like? Here is what mine is looking like and the error mysql is giving me.

    UPDATE gallery SET `order` = CASE id WHEN 11 THEN 1 WHEN 10 THEN 2 WHEN 12 THEN 3 WHEN 13 THEN 4 WHEN
    7 THEN 5 WHEN 14 THEN 6 WHEN 15 THEN 7 WHEN 16 THEN 8 WHEN 17 THEN 9 WHEN 18 THEN 10 WHEN 19 THEN 11
    WHEN 20 THEN 12 WHERE id IN (11,10,12,13,7,14,15,16,17,18,19,20)

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE id IN (11,10,12,13,7,14,15,16,17,18,19,20)’ at line 3

    Thank you kind sir.

  • Karl

    Hi Josh

    Sorry, I missed out the ‘END’ which closes the CASE in the php example. It’s now been updated. Thanks for letting me know!

    BTW the query should look like this:

    UPDATE gallery SET `order` = CASE id WHEN 11 THEN 1 WHEN 10 THEN 2 WHEN 12 THEN 3 WHEN 13 THEN 4 WHEN
    7 THEN 5 WHEN 14 THEN 6 WHEN 15 THEN 7 WHEN 16 THEN 8 WHEN 17 THEN 9 WHEN 18 THEN 10 WHEN 19 THEN 11
    WHEN 20 THEN 12 END WHERE id IN (11,10,12,13,7,14,15,16,17,18,19,20)

  • darxx

    Karl beautiful sql big tnx.
    Save a lots of time.

  • Stephen McIntyre

    Thanks for a very useful tutorial. Will help to hugely cut down my bandwidth and db queries :)

    One thing though, your fifth code box should read:

    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)

    Note the lack of the second “SET”. This caused me loads of head scratching but found the solution in the end. Hope this helps, and keep up the good work.

    Cheers

  • Karl

    Hi Stephen,

    Thanks for catching that, I’ve updated the post.

  • oneafrikan

    Thanks dude, much appreciated. Great post ;-)

  • asif

    (Thnak’s Thnak’s Thnak’s Thnak’s…….) exp infinite.I am very happy to have this..

  • Rob

    Wow, excellent article. It’s things like this that keep my interest in web development – I really like doing things more efficiently. I personally would have stopped at the for loop :)

    Many Thanks!

  • Jet Asor

    Thank you so much dude.,, your post solved my problem.!!!

  • Abdessamad IDRISSI

    Hi everybody,
    thanx karl for this smart hint.very helpful

  • Yogan

    Is there any other option to do the same without using CASE? if so please mail me the technique…. Tx in adv

  • Jomon

    I used to follow the ‘foreach’ in similar situatioins. Now got a useful hint. Thanks a lot..

  • Mikey

    When working with large tables of data where there is heavy system load, this update with one query approach can cause very serious performance issues and will generally lock a table far longer than is reasonable for other users. I have had many a query like this killed by my sysadmin because it has caused system load to go through the roof on a table with only about 500,000 rows.

  • Pingback: 10 sql tips to speed up your database

  • Pingback: 加速資料庫的小技巧 « Inet@Hack

  • Eduardo

    Hi, Mikey you says:
    “When working with large tables of data where there is heavy system load, this update with one query approach can cause very serious performance issues and will generally lock a table far longer than is reasonable for other users. I have had many a query like this killed by my sysadmin because it has caused system load to go through the roof on a table with only about 500,000 rows.”

    What is solutions….

    Thanks’s (sorry for my english)

  • Karl

    Hi Eduardo

    One option would be to run updates in batches via cron. This means that you will not lock the table for an excessive amount of time.

  • Pingback: 10 sql tips to speed up your database | Protech Mate Sdn Bhd

  • Pingback: 10 sql tips to speed up your database — The Trending Monster Info

  • Pingback: 10 sql tips to speed up your database | Son Of Byte - Web Design & Development

  • another programmer

    Excellent post and thanks a lot

  • another programmer

    can you let me know whether i can update based on values in 2 or 3 columns in the same table for this scenario?

  • Jere

    Thank you a lot. I really needed this.

  • mogyiman

    That’s very cool indeed.
    I’ve used before a trick to do something similar with selecting only once a group of values assigned to different return variables but could not figure out this could work with updates too. :)
    Here’s the select thing:

    1
    2
    3
    4
    5
    6
    7
    SELECT
    SUM(CASE WHEN status = 'originated' THEN 1 ELSE 0 END) AS originated,
    SUM(CASE WHEN status IN ('calling','connected') THEN 1 ELSE 0 END) AS online,
    SUM(CASE WHEN status = 'connected' THEN 1 ELSE 0 END) AS connected,
    SUM(CASE WHEN status = 'new' THEN 1 ELSE 0 END) AS `new`,
    SUM(CASE WHEN status = 'calling' THEN 1 ELSE 0 END) AS calling
    FROM callrequest"

    The result will be the sum of actual statuses returned in the “columns” specified with AS.

  • Kite

    Hi !

    Great tips for the update !

    I tried to use this with a form send by post and i com with that :

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    $res = mysql_query("SELECT COUNT(*) FROM rules WHERE subtype = 'upload' AND type = '".$_GET['tab']."' ") or die(mysql_error());
       $arr = mysql_fetch_row($res);
       $count = $arr[0];
       
       $rulesarray = array();
       
       for ($i=0; $i < $count; $i++) {
          $rulearray[$_POST['ruleid'][$i]] = $_POST['rule'][$i];
       }
           
       $ids = implode(',', array_keys($rulearray));
       $sql = "UPDATE rules SET text = CASE id";
       foreach ($rulearray as $id => $text) {
          $sql .= sprintf("WHEN %d THEN '%s'", $id, sqlesc($text));
       }
       $sql .= "END WHERE id IN ($ids)";
       //echo $sql;
           
       mysql_query($sql) or die(mysql_error());;

    And it works perfectly ! ^^

    Thanks a lot, before i updated all the rows and i start change my update statement like this one ^^

  • dralezero

    Thanks. I am doing the jQuery sortable so my IDs of the records I want to update are the values in my array. The array index is the ordering (keys). So I swapped the values in WHEN THEN

    1
    2
    3
    4
    5
    6
    7
    $sql = "UPDATE `listitems` SET `position` = CASE `id`";
    foreach ($_GET['listItem'] as $position => $id) {
        $sql .= " WHEN ".$id." THEN ".$position;
        $ids[] = $id;
    }
    $ids = implode(",",$ids);
    $sql .= " END WHERE `id` IN ($ids)";
  • Feroz

    Excellent post!!!

    Perfect Solution for SQL Query…

    Great post

  • Tacaza

    I’m also doing the jQuery sortable.

    I was going to implement the method described here:
    http://le-gall.net/pierrick/blog/index.php/2007/11/29/108-mysql-bulk-update-with-talend-open-studio

    but fortunately I came across this article. The solution described here is much simpler to implement and as I’ve tested myself, the query is very efficient, at least for me. Thank you very much for the post.

  • Colin

    Anyone know how to program this exact same thing using C# for ASP.NET and maybe ACCESS DB for now? :)

  • bruno negrao

    great post, thank you.

  • Kirk

    Not Working.
    Using MS Access

    UPDATE navItems
    SET navItems.pos = CASE navItems.pos WHEN 10 THEN 2 WHEN 25 THEN 10 WHEN 24 THEN 13 ELSE 0 END
    WHERE (pos IN (10, 25, 24))

    Receiving a syntax error.

    Any ideas?

    Kirk

  • zflag

    Kirk: your query doesn’t work because your CASE statement is evaluating the same field you are trying to update.

  • Jay

    The use of the CASE statement to update multiple records only works, if you’re updating less than 255 rows – there is a limit on the number of CASE statements you can use.
    So, if you have more than 255 rows to update, then just write it as multiple queries, using the very first syntax described above.

  • Karl

    @Jay

    The limit in the number of case statements does not mean the number of rows which can be updated is limited to 255 – the two factors are not necessarily related. You might have 10 case statements which could match 100000 rows each for example. If you are trying to target a single row per case statement then you may have an issue however. If this is the case you would probably be better off moving the conditions into their own table and joining to achieve the same thing:

    1
    2
    3
    4
    5
    category_order_helper
    category_id  | display_order
    -------------+----------------
    1            | 6
    2            | 4
    1
    2
    3
    4
    UPDATE categories
    SET display_order = h.display_order
    INNER JOIN category_order_helper AS h
    ON categories.id = h.category_id

    It requires a couple of extra queries (a TRUNCATE and an INSERT), but it avoids the case statement limit.

  • jy

    i posted on this thread in adobe dw forums,since the code got cut off

    http://forums.adobe.com/thread/713810

  • Pingback: CAP Marketer - 10 astuces SQL pour accélérer vos bases de données

  • Fred

    So much useful this your post.
    Were looking an sql script to do this.

    _o/

  • abhijeet srivastav

    Does this logic (multiple row UPDATES with CASE) works in Mainframe DB2 environment
    I am using DB2 Version 9.1

    Highly appreciate a quick response.

  • HJ

    Thnx a ton Karl … a bit late though, but an awesome query .. HJ

  • HJ

    Karl one more thing .. how do I use the query with a loop ? what i am trying is something like this
    for ($i= $seq2; $i $i + 1 );

    the same aint working ?!?!

  • Karl

    Hi HJ,

    I’m not sure I follow your question, but the syntax of your for loop is incorrect.

    1
    2
    3
    for ($i = $seq2; $i < $something; $i++) {
        // ...
    }

    If that’s not your issue I think you’ll need to elaborate further.

  • HJ

    oops .. sorry Karl ! What I wanted to say was, if I use ‘for’ loop , the query doesn’t work but the query works if the same thing is done manually

    //doesn’t work with loop
    for ($i= $seq2; $i 2,
    2 => 3,
    3 => 4,
    4=> 5,
    5=> 6,
    6=> 7,
    7=> 8

  • HJ

    //doesn’t work with loop
    for ($i= $seq2; $i 2,
    2 => 3,
    3 => 4,
    4=> 5,
    5=> 6,
    6=> 7,
    7=> 8

  • HJ

    //doesn’t work with loop
    for ($i= $seq2; $i<= $count + 1 ; $i++)
    $seq_no = array(
    $i = $i + 1
    );

  • HJ

    * sorry for the first 2 comments

  • Haris

    Thanks for the time your help has saved me :)

  • Marco Saluzzo

    Thanks for putting this example on-line. This has given me a beautiful solution to a problem I had.

  • Pingback: Update Multiple Rows With Different Values and a Single SQL Query | Unhandled Perception

  • Mohammadreza

    THANKS A LOTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT

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>