具有多个join子查询的mysql update count查询

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:

早上，我正在尝试使用UPDATE，JOIN，SUBQUERY创建每个类别/商店的产品数量，但我无法正确使用语法，有人可以在我出错的地方帮助我：

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)

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.

基本上这是在OpenCart中，但现有的产品计数例程是在PHP中完成的，在大型DB产品上，它会使网站速度降低。

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.

产品使用product_to_category和product_to_store表，类别按ID链接到类别和商店，并使用category_to_store表链接到商店。

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.

我想运行mySQL查询，通过将值存储在category_to_store表中来更新每个类别/存储的产品计数，因为这只需要在添加/删除/更新产品时运行，而不是每次加载任何页面时都运行。

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;

2 个解决方案

#1

You need to use the INNER JOIN syntax:

您需要使用INNER JOIN语法：

UPDATE 
    oc_category_to_store a
INNER JOIN 
(
    SELECT
        b.category_id,
        c.store_id,
        COUNT(*) AS productcnt
    FROM 
        oc_product a
    INNER JOIN
        oc_product_to_category b ON a.product_id = b.product_id
    INNER JOIN
        oc_product_to_store c ON a.product_id = c.product_id
    GROUP BY
        b.category_id,
        c.store_id
) b ON 
    a.category_id = b.category_id AND 
    a.store_id = b.store_id
SET
    a.product_count = b.productcnt

#2

The 1st error is in your query logic. Your subquery returns 3 columns:

第一个错误出现在查询逻辑中。您的子查询返回3列：

product_count,
p2c.category_id,
p2s.store_id

And you're trying to assign this triple to a single column *c2s.product_count*

并且您正在尝试将此三元组分配给单个列* c2s.product_count *

UPDATE

The 2nd error is in the WHERE clause of your main query. You use tables p2c, p2s and pc however UPDATE clause utilizes c2s only. The most likely the WHERE clause must be in the subquery like below:

第二个错误位于主查询的WHERE子句中。您使用表p2c，p2s和pc但UPDATE子句仅使用c2s。 WHERE子句最有可能在子查询中，如下所示：

UPDATE 
    oc_category_to_store c2s 
SET 
    c2s.product_count = 
        (
        SELECT 
            count(oc_product.product_id) as product_count,
        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)
        WHERE
            (p2c.category_id = pc.category_id) AND
            (p2s.store_id = pc.store_id)        
        )

#1