tsql - "Incorrect syntax near 'WHERE'" in Delete queries

Question

For about the past year I having an issue with having my delete queries recognize a where clause. I am not talking about big major complex queries. Even little simple queries.

Usually I am in a hurry so rather than try to analyze the issue I simply utilize a join on a subquery to specify which rows to delete. But now I want to know what's going on. I've looked other places on stackoverflow but those errors were always due to something obvious like a extra comma, or trying to use the where clause with an Insert values statement.

The culprit today is the following:

DELETE FROM prc_ContractChanges chgs
WHERE chgs.ChangeTypeID = 1 

Similar constructs have worked fine for the last 19 years. So why all of a sudden does this throw the obscene error

Incorrect syntax near 'WHERE'.

I tried to add a comment in response to one below but each attempt end with "an error occurred when adding this comment" so UI am editing the original question

That suggestion below worked. So it occurred to me how would you identify the multiple tables in a delete with a join. So I put the alias in the delete clause. That worked. It occurred to me that putting the alias in the delete clause with a single table delete would work as well. It did.

标签： tsqlsql-server-2014

Answer 1

You can use one of the following syntaxes:

DELETE prc_ContractChanges 
FROM prc_ContractChanges chgs
WHERE chgs.ChangeTypeID = 1 

DELETE prc_ContractChanges 
WHERE ChangeTypeID = 1