Why can't I use an alias in a DELETE statement?

178
votes

In SQL Server Compact Edition in Visual Studio 2010 (maybe SQL Server and SQL in general, I don't know), this command works:

DELETE FROM foods WHERE (name IN ('chickens', 'rabbits'))

but this command produces an error of: Error near identifier f. Expecting OUTPUT.

DELETE FROM foods f WHERE (f.name IN ('chickens', 'rabbits'))
2
sqlsql-serversql-server-ce
@aaron-bertrand Thanks for correcting my title as well. I didn't realise the correct term for what I posted (otherwise google could have resolved this quickly). Thank you again. – Ricardo Altamirano
No worries. Just trying to make it clear for other readers. – Aaron Bertrand
I do agree with you by the way that the syntax variations between different commands is a little unintuitive at times. – Aaron Bertrand
Here's the same question, but for UPDATE statements: stackoverflow.com/questions/31551/… – Daniel Neel

2 Answers

267
votes

To alias the table you'd have to say:

DELETE f FROM dbo.foods AS f WHERE f.name IN (...);

I fail to see the point of aliasing for this specific DELETE statement, especially since (at least IIRC) this no longer conforms to strict ANSI. But yes, as comments suggest, it may be necessary for other query forms (eg correlation).

84
votes

The delete statement has strange syntax. It goes like this:

DELETE f FROM foods f WHERE (f.name IN ('chickens', 'rabbits'))