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

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

Solution

To alias the table you'd have to say:

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

For a statement this simple, though, this will do and doesn't require an alias:

DELETE dbo.foods WHERE name IN (...);

But yes, as comments suggest, an alias may be necessary for other query forms (e.g. any DML combined with correlation, joins, EXISTS, etc). In SQL Server you can do this using, for example:

DELETE f
  FROM dbo.foods AS f
  INNER JOIN dbo.allergies AS a
  ON f.FoodId = a.FoodId;

Just keep in mind this query may have to be constructed differently on {not SQL Server}.