SQL | Update multiple rows with one query

Question

I have a method, where about 20 rows must changed. Im passing per ajax a string like "14-33-61-10 ..." for saving the order of some rows. Here is my actual (working) code:

$explode = explode('--',$_POST['data']); // -> array('0'=>'8', '1'=>'4', '2'=> ... )
$i = 1;
foreach( $explode as $task ) {
    if( !is_int($task) ) continue;
    $exec = $project->exec("UPDATE tasks SET pos=$i WHERE rowid=". $task );
    $i++;
}

My problem is, that it takes about 1 second. My question: Is there a way to edit multiple rows with just one query?

check this out `http://stackoverflow.com/questions/9346755/update-multiple-rows-in-1-column-in-mysql` — Drixson Oseña, Aug 06 '13 at 09:32

Andomar · Answer 1 · 2013-08-06T10:11:59.433

3

Sure, send a SQL string like:

update  tasks 
set     pos =
        case rowid
        when 13 then 1
        when 33 then 2
        when 61 then 3
        when 10 then 4
        end
where   rowid in (13,33,61,10)

The translation from 13-33-61-10 to (13,33,61,10) is best done in PHP.

edited Aug 06 '13 at 10:11

answered Aug 06 '13 at 09:31

Andomar

232,371
49
380
404

@mr_app base on code "set_pos=$i" is already incremental.. try putting your update statement after "i++" – zxc Aug 06 '13 at 10:00
Good point, SQL snippet updated. You can also send multiple update statements in one go `update ...; update ...; update ...;` – Andomar Aug 06 '13 at 10:12
Thank you. I tried both "update...,update..." and your solution. Your one one is 3 times faster. – mr_app Aug 06 '13 at 11:09
Awesome, I never knew you could do an update in a single statement. Very nice. – cnizzardini Dec 10 '13 at 04:19

score 0 · Answer 2 · answered Aug 06 '13 at 10:27

If the range is short then we can use the ID IN (ids) argument.

UPDATE `profile` SET `app_status`=3  WHERE `id` IN (3,37,95,136,1087,1795,1817)

If it's long and we know that range, let's use the BETWEEN value AND value argument.

UPDATE `profile` SET `app_status`=3  WHERE `id` BETWEEN 3904 AND 3918

score -1 · Answer 3 · answered Aug 06 '13 at 09:32

$explode = explode('--',$_POST['data']); // -> array('0'=>'8', '1'=>'4', ... )
$i = 1;
$sql = "";
foreach($explode as $task)
{
    if(!is_int($task)) continue;
    $sql .= "UPDATE tasks SET pos=$i WHERE rowid=$task;";
    $i++;
}
$project->exec($sql);

SQL | Update multiple rows with one query

3 Answers3