Where not exists in delete

Question

A simple issue I'm having. I'm getting a syntax error from the following SQL:

DELETE FROM Blog.Category c 
WHERE NOT EXISTS (SELECT * FROM Blog.Posts p
                  WHERE p.postCategory = blog.Category.catId)

Obviously I'm missing something silly here. Any hints would be appreciated.

EDIT

I have also tried

DELETE FROM Blog.Category c 
where NOT EXISTS(SELECT * FROM Blog.Posts p WHERE  p.postCategory = c.catId)

However I get

Incorrect syntax near 'c'

Answer 1

You are missing the alias directly after the DELETE statement.

DELETE c FROM Blog.Category c 
WHERE NOT EXISTS(SELECT * FROM Blog.Posts p WHERE  p.postCategory = c.catId)

---

Alternatively you can omit the alias and use the full table name.

DELETE FROM Blog.Category
WHERE NOT EXISTS(SELECT * FROM Blog.Posts p WHERE  p.postCategory = Blog.Category.catId)

Answer 2

I like updating and deleting alias'd tables better as I feel it is more clear.

DELETE c
FROM Blog.Category c
left join Blog.Posts p ON p.postCategory = c.catId
WHERE p.postCategory IS NULL

The issue in your query is you alias the table and then don't use it in the EXISTS.