Why doesn't MySQL DELETE work with a subquery?

Question

MySQL

Some categories have been removed in OpenCart, but the products remain, they also need to be removed.
Wrote such a query, but it does not work (rows are not deleted):

delete from %DB_PREFIX%product where product_id in (select distinct p.product_id from %DB_PREFIX%product p left join %DB_PREFIX%product_to_category p2c on p.product_id = p2c.product_id where isnull(p2c.category_id)) -- не работает

And this request works correctly:

select * from %DB_PREFIX%product where product_id in (select distinct p.product_id from %DB_PREFIX%product p left join %DB_PREFIX%product_to_category p2c on p.product_id = p2c.product_id where isnull(p2c.category_id)) -- OK

There are many tables in OpenCart: product_image, product_to_download, product_description and others, so the necessary rows were deleted from them, but not from the product table.

delete from %DB_PREFIX%product_image where product_id in (select distinct p.product_id from %DB_PREFIX%product p left join %DB_PREFIX%product_to_category p2c on p.product_id = p2c.product_id where isnull(p2c.category_id)) -- OK

Answer 1

After attempting to run a query, check its result and errors. In this case, it is not possible to delete from the same table from which data is selected in the subquery.
Or split your query into 2 different ones with . Or rewrite it differently:CREATE TEMPORARY TABLE ... SELECT ...

DELETE FROM %DB_PREFIX%product WHERE product_id NOT IN (SELECT DISTINCT product_id FROM %DB_PREFIX%product_to_category)

Answer 2

Execute nested and use results mostly