mysql - 使用带有来自另一个表的左连接和 group_concat 的子查询更新字段

Question

我正在使用下一个代码，但它失败了，我不知道为什么。

UPDATE pd
SET pd.meta_categories = x.categories
FROM t_product_description pd
JOIN (
    SELECT p.product_id AS productid, GROUP_CONCAT(cd.name SEPARATOR ' ') AS categories
    FROM t_product AS p
    LEFT JOIN t_product_to_category AS p2c ON (p2c.product_id = p.product_id)
    LEFT JOIN t_category_description AS cd ON (cd.category_id = p2c.category_id)
    GROUP BY p.product_id
) x ON pd.product_id = x.productid

Answer 1

在 mysql 中，join 子句成为第一个，(不需要 from )和 set 子句 and

UPDATE t_product_description pd
JOIN (
    SELECT p.product_id AS productid, GROUP_CONCAT(cd.name SEPARATOR ' ') AS categories
    FROM t_product AS p
    LEFT JOIN t_product_to_category AS p2c ON (p2c.product_id = p.product_id)
    LEFT JOIN t_category_description AS cd ON (cd.category_id = p2c.category_id)
    GROUP BY p.product_id
) x ON pd.product_id = x.productid
SET pd.meta_categories = x.categories