MySQL基于同一表的COUNT更新

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'的目标，又作为数据的单独源

我明白我需要应用不同的别名,因此我试着:

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 - where子句中未知列'c.id'

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;