title: mysql update count query with multiple join subquery
body: <p>Morning,
  I am trying to create a count of products per category / store using an UPDATE, JOIN, SUBQUERY but I cant get the syntax right, can someone help me where I am going wrong:</p>
  
  <pre><code>UPDATE 
      oc_category_to_store c2s 
  SET 
      c2s.product_count = 
          (
          SELECT 
              count(p.product_id) as product_count,
              p2c.category_id,
              p2s.store_id
          FROM 
              oc_product p
              INNER JOIN oc_product_to_category p2c ON 
                  (p.product_id = p2c.product_id)
              INNER JOIN oc_product_to_store p2s ON
                  (p.product_id = p2s.product_id)
          ) AS pc
  WHERE
          (p2c.category_id = pc.category_id) AND
          (p2s.store_id = pc.store_id)
  </code></pre>
  
  <p>Essentially this is in OpenCart but the existing product count routines are done in PHP are on large DBs of products it slows the site down to a crawl.</p>
  
  <p>products are linked to both categories and stores by ID using the product_to_category and product_to_store tables, categories and also linked to stores using a category_to_store table.</p>
  
  <p>I want to run a mySQL query to update the product counts per category / store by storing the value in the category_to_store table as this only needs to run when products are added / deleted / updated and not every time any page loads.</p>
  
  <pre><code>CREATE TABLE `oc_product_to_category` (
    `product_id` int(11) NOT NULL,
    `category_id` int(11) NOT NULL,
    PRIMARY KEY (`product_id`,`category_id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AVG_ROW_LENGTH=9 ROW_FORMAT=FIXED;
  
  CREATE TABLE `oc_product_to_store` (
    `product_id` int(11) NOT NULL,
    `store_id` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`product_id`,`store_id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AVG_ROW_LENGTH=9 ROW_FORMAT=FIXED;
  
  CREATE TABLE `oc_category_to_store` (
    `category_id` int(11) NOT NULL,
    `store_id` int(11) NOT NULL,
    `product_count` int(11) DEFAULT NULL,
    PRIMARY KEY (`category_id`,`store_id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AVG_ROW_LENGTH=9 ROW_FORMAT=FIXED;
  </code></pre>