1

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.

2
  • 1
    Try DELETE category instead of DELETE category.*. Commented Apr 15, 2014 at 13:00
  • 1
    What do you mean No longer runs. What happens when you try to run it? Commented Apr 15, 2014 at 13:09

1 Answer 1

1

I think Binaya Regmi is correct. When deleting in SQL-Server explicitly name the table you want to delete the rows from - especially necessary when using a join.

So in your case change the top row from

DELETE category.*

to

DELETE category
3
  • Beat me to it! I've always felt that syntax to be a little un-intuitive. Commented Apr 15, 2014 at 13:38
  • @StevePettifer you can make it perhaps more intuituve by supplying the optional FROM: DELETE FROM category etc
    – AakashM
    Commented Apr 16, 2014 at 8:10
  • @AakashM That's not the point I was making: I meant I never understood why Access used tablename.* given the .* is redundant yet intuitively sort of makes sense to a lot of beginners thanks to SELECT *. But anyway, the OP got their answer and everyone's a winner. Commented Apr 16, 2014 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.