I need to update the categories table based on two conditions:

1. The category must be top
2. The category must have at least 5 child categories

The following query:

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

Returns the error:

#1093 - Table 'c' is specified twice, both as a target for 'UPDATE' and as a separate source for data

I understand that I need to apply a different alias to the table, therefore I tried:

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

But this returns another error:

#1054 - Unknown column 'c.id' in 'where clause'

CodePudding user response：

Join the table to a query that returns the categories with at least 5 child categories:

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;