MySQL:使用update/select更新两列的组函数无效

Question

我正在尝试从MySQL 5.7中的select语句更新表中的两列。我得到的错误是“组函数的无效使用”

Stmt：

UPDATE
    catalog mpc
JOIN
    reviews mpr  ON mpr.merchant_id = mpc.MERCHANT_ID and mpr.sku = mpc.ARTICLE_ID
SET
    mpc.RATING = avg(mpr.rating),
    mpc.RATINGS = count(mpr.rating)
WHERE
        mpr.MERCHANT_ID = 1
    AND mpr.sku = '133';

在我看来，这是什么问题呢？

Answer 1

必须先在reviews中聚合，然后连接到catalog

UPDATE catalog mpc
INNER JOIN (
  SELECT merchant_id, sku, AVG(rating) avg_rating, COUNT(rating) count_rating
  FROM reviews 
  WHERE merchant_id = 1 AND sku = '133'
  GROUP BY merchant_id, sku
) mpr ON mpr.merchant_id = mpc.MERCHANT_ID and mpr.sku = mpc.ARTICLE_ID
SET mpc.RATING = mpr.avg_rating,
    mpc.RATINGS = mpr.count_rating