Ricardo Altamirano Ricardo Altamirano - 3 months ago 6
SQL Question

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

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'))


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 correlation in other query forms.