Bulk update in pure mysql instead of php?

Question

I am using the following php code to place the minimum price of a product from the 'prices' table to the 'product' table.

I have about 10.000 products with more than 500 prices each, so the task is quite demanding.

Is it possible to do this in a single mysql query without any php code?

 $last_year_date = date("Y-m-d",strtotime('first day of last year'));

 for ($i=1; $i<=10000; $i++){ 

 $query_min_p = "SELECT min(price) as min FROM prices WHERE (product_id = {$i} AND price > 0 AND price_date >= '".$last_year_date."' )";
 $result_min_p = mysql_query($query_min_p);

        while($row = mysql_fetch_array($result_min_p)){
             if ($row['min'] > 0 ){
                $query = "UPDATE products SET min_p = '".$row['min']."' WHERE (product_id = {$i})";
                $result = mysql_query($query);                 
             }
        }

  }

[Please don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php#answer-12860140). — Joseph Silber, Jan 22 '13 at 01:56

score 2 · Accepted Answer · answered Jan 22 '13 at 01:55

2

You can have a subquery in the update, as in:

update products
    set min_p = (select min(price) from prices where prices.product_id = product.product_id)

This will run much faster if you have an index on prices(product_id, price).

answered Jan 22 '13 at 01:55

Gordon Linoff

1,242,037
58
646
786

Bulk update in pure mysql instead of php?

1 Answers1