mysql - 如何使用where子句中的子查询更新表

Question

我有一个像这样的主表类别

categories_id | categories_status | parent_id
     1                  1               0
     2                  1               0
     3                  1               1
     4                  1               2
     5                  1               1
     6                  1               2

和引用表 products_to_categories

categories_id | products_id
      3             778
      3             998
      5             666
      5             744

我选择所有没有子类别的类别:

SELECT * FROM categories
WHERE categories_id not in ( SELECT parent_id FROM categories )
# gets entries with id 3, 4, 5, 6

引用表中没有产品:

AND categories_id NOT IN ( SELECT categories_id FROM products_to_categories )
# gets entries with id 4, 6

现在我想更新这个结果的 categories_status，但仅仅将 SELECT 更改为 UPDATE 是行不通的:

UPDATE categories
SET categories_status = 0
WHERE categories_id not in ( SELECT parent_id FROM categories )
AND categories_id NOT IN ( SELECT categories_id FROM products_to_categories )

有几个类似的问题，但我不知道如何改变我的例子......

谢谢和最好的问候，

亚历克斯

Answer 1

使用左外连接代替子查询

update 
categories c1
left outer join categories c2 on c1.ID = c2.Parent_id
left outer join products_to_categories p on c1.categories_id = p.categories_id
set c1.categories_status = 0
where c2.ID is null and p.categories_id is null