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.*. Apr 15 '14 at 13:00
  • 1
    What do you mean No longer runs. What happens when you try to run it? Apr 15 '14 at 13:09
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. Apr 15 '14 at 13:38
  • @StevePettifer you can make it perhaps more intuituve by supplying the optional FROM: DELETE FROM category etc
    – AakashM
    Apr 16 '14 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. Apr 16 '14 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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