title: mySQL Nested Update using COUNT/MIN/MAX from another Table
body: <p>I have two large tables, products (500k records) and store_products  (> 3mm records). Products is the master and product_stores is individual locations with the product.</p>
  
  <p>I need to run a single QUERY totaling up information from product_stores and updating the corresponding product.</p>
  
  <p>When this was smaller dataset we did it with a nested query:</p>
  
  <pre><code>SELECT productid,COUNT(id) as count,MIN(price) as lowprice,MAX(price) as highprice FROM store_products
  WHILE (productid){ update product set stores = count, min = lowprice, max = highprice WHERE productid = $productid }
  GROUP BY productid
  </code></pre>
  
  <p>I'm fairly new to nested updates and uncertain how to set multiple fields with a join and group by.</p>
  
  <p>Structure [truncated to relevant fields]:</p>
  
  <pre><code>CREATE TABLE product ( 
  product_id INT UNSIGNED NOT NULL AUTO_INCREMENT,     
  stores INT UNSIGNED NOT NULL DEFAULT '0',    
  lowprice DECIMAL (6,2) NOT NULL DEFAULT '000.00', 
  highprice  DECIMAL (6,2) NOT NULL DEFAULT '000.00', 
  PRIMARY KEY (product_id), 
  KEY stores (stores) 
  )
  
  CREATE TABLE store_product (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,    
  product_id INT UNSIGNED NOT NULL,
  price DECIMAL(7,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (storeproduct_id),
  KEY product_id (product_id)
  );
  </code></pre>
  
  <p>Fields to update: </p>
  
  <ul>
  <li>stores [count of store_product records by productid]</li>
  <li>min price [MIN of price by productid]</li>
  <li>max price [MAX of price by productid]</li>
  </ul>