Update multiple rows for 1000 records in one go

Question

I have one table based on which one I have to update 6 rows in the other table for matching ids. It is total of over 1000 records so most of the time I get timeout error with current script.

The way I do it now is, I select the range of ids between two dates from the first table, store it into an array and then run foreach loop making update in the second table where the ids are the same, so basically I run a query for every single id.

Is there anyway I could speed it up the process?

I found only a way to generate the each within the foreach loop

UPDATE product SET price = CASE
WHEN ID = $ID1 THEN $price1
WHEN ID = $ID1 THEN $price2
END

But I don't know how could I modify this to update multiple rows at the same time not just one.

My script code look like that

  $sql = "SELECT * FROM `games` where  (ev_tstamp >= '".$timestamp1."' and ev_tstamp <= '".$timestamp2."')";

  while($row = mysqli_fetch_array($sql1)){ 
  $one_of =[
  "fix_id" =>$row['fix_id'],   
  "t1_res" =>$row['t1_res'],
  "t2_res" =>$row['t2_res'],
  "ht_res_t1" =>$row['ht_res_t1'],
  "ht_res_t2" =>$row['ht_res_t2'],
  "y_card_t1" =>$row['y_card_t1'],
  "y_card_t2" =>$row['y_card_t2'],
  "t1_corners" =>$row['t1_corners'],
  "t2_corners" =>$row['t2_corners'],
  "red_card_t1" =>$row['red_card_t1'],
  "red_card_t2" =>$row['red_card_t2']
   ];
   array_push($today_games,$one_of); 
    }

   foreach($today_games as $key=>$val){
        $cards_t1=$val['red_card_t1']+$val['y_card_t1'];
        $cards_t2=$val['red_card_t2']+$val['y_card_t2'];
   $sql = "Update sights SET t1_res='".$val['t1_res']."', 
     t2_res='".$val['t2_res']."', ev_tstamp='".$val['ev_tstamp']."',
     ht_res_t1='".$val['ht_res_t1']."', ht_res_t2='".$val['ht_res_t2']."', 
     t1_corners='".$val['t1_corners']."',t2_corners='".$val['t2_corners']."',
     t1_cards='".$cards_t1."',t2_cards='".$cards_t2."'
     where fix_id='".$val['fix_id']."' "
    }

*I get timeout error with current script*. What is your current script? — GMB, Aug 23 '20 at 00:10
Could you please show your tables? Based on the above, you should be able to do an update using a `join` which would tremendously simplify your query. Actually, your `case when...` statement seems to be doing what would amount to a sort of crude join if I'm understanding the question correctly. — David542, Aug 23 '20 at 00:13
It has all been asked before. You can use INSERT INTO ... ON DUPLICATE KEY UPDATE syntax as described at https://stackoverflow.com/questions/3432/multiple-updates-in-mysql — Lupinity Labs, Aug 23 '20 at 00:13
How does first SQL update query related to update in PHP? Also, PHP updates **all** rows of `sights` since no `WHERE` condition is used. Finally, look into [SQL parameterization](https://stackoverflow.com/q/60174/1422451) and not messy string interpolation, — Parfait, Aug 23 '20 at 00:48
The first sql just select the data and then update the other table where is match on the fix_id, sorry it was missing that part — user2741313, Aug 23 '20 at 00:54

score 1 · Answer 1 · answered Aug 23 '20 at 01:02

Consider an UPDATE...JOIN query using fix_id as join column. Below runs mysqli parameterized query using timestamps. No loop needed.

$sql = "UPDATE sights s
        INNER JOIN `games` g 
             ON s.fix_id = g.fix_id
             AND g.ev_tstamp >= ? and g.ev_tstamp <= ? 
        SET s.t1_res.    = g.t1_res, 
            s.t2_res.    = g.t2_res, 
            s.ev_tstamp  = g.ev_tstamp, 
            s.ht_res_t1  = g.ht_res_t1, 
            s.ht_res_t2  = g.ht_res_t2, 
            s.t1_corners = g.t1_corners,
            s.t2_corners = g.t2_corners,
            s.t1_cards   = (g.red_card_t1 + g.y_card_t1),
            s.t2_cards   = (g.red_card_t2 + g.y_card_t2)";

$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, 'ss', $timestamp1, $timestamp2);

mysqli_stmt_execute($stmt);

Update multiple rows for 1000 records in one go

1 Answers1