在纯mysql而不是php中批量更新？

问题描述：

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);                 
             }
        }

  }

我使用以下php代码将产品的最低价格从“价格”表下放到' 产品'表。 p>

我有大约10,000种产品，每种产品价格超过500，因此任务非常苛刻。 p>

是否可以在没有任何php代码的单个mysql查询中执行此操作？ p>

  $ last_year_date = date（“Ymd”，  strtotime（'去年的第一天'））; 
 
 for（$ i = 1; $ i＆lt; = 10000; $ i ++）{
 
 $ query_min_p =“SELECT min（price）as min FROM price  WHERE（product_id = {$ i} AND price＆gt; 0 AND price_date＆gt; ='“。$ last_year_date。”'）“; 
 $ result_min_p = mysql_query（$ query_min_p）; 
 
 while（$ row = mysql_fetch_array  （$ result_min_p））{
 if（$ row ['min']＆gt; 0）{
 $ query =“UPDATE products SET min_p ='”。$ row ['min']。“'WHERE（product_id =  {$ i}）“; 
 $ result = mysql_query（$ query）;  
} 
} 
 
} 
   code>  pre> 
  div>

答

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).

在纯mysql而不是php中批量更新？

相关推荐