how to update table with subquery in where clause

Question

i have a main table categories like this

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

and a reference table products_to_categories

categories_id | products_id
      3             778
      3             998
      5             666
      5             744

I select all the categories with no child-category:

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

and no products in reference table:

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

Now i would like to update the categories_status for this result but it don't work just changing SELECT to 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 )

There a few similar questions, but i can't figure out how to change my example...

Thanks & best regards,

Alex

Solution 1

Instead of sub-queries, use a left outer join

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

Solution 2

You cannot update records contained in subquery where conditions. Try to use direct conditions like:

// this rely that all categories_id higher than 0 should be valid relations
WHERE categories_id < 1

..this should work, but i would like to give you advice related to performance - use NULL values in case of records without parents. Than the condition should be WHERE categories_id IS NULL