基于条件的同一表的计数mySQL更新

Question

我需要根据两个条件更新类别表：

1. 类别必须为top
2. 该类别必须至少具有5个儿童类别。

以下查询：

UPDATE categories AS c
SET c.columns = 4
WHERE c.top = 1 AND (SELECT COUNT(*) FROM categories WHERE parent_id = c.id) >= 5

返回错误：

＃1093-表'C'指定了两次，既是“更新”的目标，也是数据的单独来源

我知道我需要在表上应用不同的别名，因此我尝试了：

UPDATE categories AS c
SET c.columns = 4
WHERE c.top = 1 AND (SELECT COUNT(*) FROM (SELECT * FROM categories WHERE parent_id = c.id) AS c2) >= 5

但是这返回了另一个错误：

＃1054-未知列'c.id'in where子句'

Answer 1

将表加入至少5个儿童类别的类别的查询：

UPDATE categories AS c
INNER JOIN (
  SELECT parent_id
  FROM categories 
  GROUP BY parent_id
  HAVING COUNT(*) > 5
) AS t ON t.parent_id = c.id
SET c.columns = 4
WHERE c.top = 1;