I have a legacy query from years ago that was written for an MS Access backend:
DELETE category.* FROM category LEFT JOIN product_category ON category.id = product_category.categoryid WHERE product_category.categoryid IS NULL AND category.id IN (SELECT cat.id FROM category AS cat LEFT JOIN category AS cat_par ON cat_par.category_parent = cat.id WHERE cat_par.category_parent IS NULL AND cat.category_parent != 0 AND cat.link IS NULL);
My system was changed to SQL Server and this query no longer runs. I have tested the subquery (in the parenthesis) separately and that works as expected. Just no records are getting deleted.
I have tried changing the first two lines to read
DELETE FROM category (the only syntax difference I was able to look up) but still no deletion occurs.