Mysql 使用带有子查询的更新

Question

我想通过在 where 子句中使用子查询来更新表，但出现异常“您无法在 FROM 子句中指定目标表'catalog_category for update'”。这是我的查询:

update catalog_category set `status` = "inactive"
where id_catalog_category in ( 
SELECT id_catalog_category
         FROM catalog_category t1 where (SELECT status
         FROM catalog_category t2
         WHERE t2.lft < t1.lft AND t2.rgt > t1.rgt
         ORDER BY t2.rgt-t2.lft ASC limit 1) = 'active' and status = 'inherited_inactive')

有什么办法可以修复这个错误吗？

Answer 1

使用以下sql进行更新:-

update catalog_category cc
      join (SELECT id_catalog_category
                 FROM catalog_category t1 where (SELECT status
                 FROM catalog_category t2
                 WHERE t2.lft < t1.lft AND t2.rgt > t1.rgt
                 ORDER BY t2.rgt-t2.lft ASC limit 1) = 'active' and status = 'inherited_inactive') tmp
    on cc.id_catalog_category = tmp.id_catalog_category
    set cc.`status` = "inactive"