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

by

To alias the table you’d have to say:

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

…though I fail to see the point of aliasing for this specific statement, especially since (at least IIRC) this no longer conforms to strict ANSI, may cause unnecessary hurdles when writing for multiple platforms, and it introduces complexity and confusion for new users learning the basics of vanilla DML.

This will do and doesn’t require an alias:

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

But yes, as comments suggest, it 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}.

Leave a Comment