How to update a field multiple times?

Question

I have an array containing about a 50 items. Each item is an ID => price, and I have a table called products, I need to update the price of the product to the new price in the array which is referenced using the id

I could use a foreach loop and do the following

foreach($products as $ID => $price) {
    $result = mysqli_query($link, "UPDATE product SET price = $price WHERE id = $ID");
}

But that will ultimately end up in me running almost 50 queries, is there a faster way to do it or a better practice?

possible duplicate of [Multiple Updates in MySQL](http://stackoverflow.com/questions/3432/multiple-updates-in-mysql) — Jeremy Harris, Jul 30 '14 at 16:42

A Boy Named Su · Accepted Answer · 2014-07-30T16:57:35.167

You should create an update case query in the format:

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

You can use a loop to generate the each case and execute a single query.

For safety you should probably use a where clause as well, to prevent changing prices on rows where the ID was not present in a case.

So to revise the query:

UPDATE product SET price = CASE
WHEN ID = $ID1 THEN $price1
WHEN ID = $ID1 THEN $price2
END
WHERE ID IN ($IDs)

And for a working example:

$ids = array();
$cases = array();

foreach ($products as $id => $price) {
    $ids[] = $id;

    $cases[] = sprintf("WHEN ID=%s THEN '%s'", $id, $price);
}

$query = "UPDATE products SET PRICE CASE %s END WHERE ID IN(%s)";
$args = array(
    implode(" ", $cases),
     implode(", ", $ids)
);

$updateQuery = vsprintf($query, $args);

NaveenThally · Answer 2 · 2014-07-30T17:56:30.657

You should Use CASE Statement of MYSQL

Below is the small Example

  update products set price=case id
  when '6' then '2'
  when '7' then '3' 
  end
  where id in (6,7);

What the above code does is includes a case statement

when 6 then 2 explains when id=6 then set the price =2 from the above example

 $casear=array();
 $incase=array(); 
foreach($products as $id=>$price)
{
   array_push($casear, "when '$id' then '$price'");
   array_push($incase,'$id');
} 
$casequery= implode('',$casear);
$inquery=implode(',',$incase)

//Finally executing your query 
$upquery="UPDATE Products set price=case id $casequery end where id in($inquery)";
$result=mysqli_query($link,$upquery);

How to update a field multiple times?

2 Answers2