215

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'))
Ricardo Altamirano
  • 14,650
  • 21
  • 72
  • 105

2 Answers2

317

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}.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 3
    I was mainly just curious, because I normally use aliases when using `SELECT` and other such statements, so I instinctively did what I'm used to and was wondering why it didn't work properly. – Ricardo Altamirano Jun 12 '12 at 21:38
  • 54
    +1 In the OP's case, aliasing may not be needed, but it was helpful to me because I was using an EXISTS clause, so I had to alias the table so I could tie both queries together. – Ricardo Jan 13 '14 at 15:46
  • 4
    I was looking for a solution when taking an existing SELECT query and turning it into a DELETE statement quickly without having to rewrite the aliasing. – Alex May 19 '15 at 13:54
  • Strange syntax, but unfortunately this is *required* if deleting from a table variable -- see http://stackoverflow.com/questions/597513/table-variables-with-an-alias-in-a-delete-from-statement – Abacus Mar 11 '16 at 22:41
  • 6
    Example of a use case where this is important; deleting based on contents of a second table where there are multiple columns involved (i.e. so `in` or `not in` wouldn't work: `DELETE f from dbo.foods as f where not exists (select top 1 1 from animalDiets a where a.AnimalId = f.AnimalId and a.DietId = f.DietId)` – JohnLBevan Jul 20 '16 at 09:22
  • 6
    The delete with an alias is useful when you want to delete from a table but need to join that table to other tables/views to get a reduced set of rows. E.g. `delete o from Order as o inner join Customer as c on c.CustomerID = o.CustomerID where c.ArchiveOrders = 1` – Andrew Jens Aug 31 '17 at 02:03
  • Aliasing allows you to use autocomplete for column names when you're writing the statement, which is convenient for ad-hoc deletes. – Jamie Dec 17 '20 at 15:11
  • @Jamie [You don't need an alias for autocomplete to work](https://i.stack.imgur.com/KqoT4.png), particularly when the statement (like the one in the question) only references a single table. – Aaron Bertrand Dec 19 '20 at 00:02
  • @AndrewJens Yes, I fully understand why an alias is useful _when there is correlation_. One more time, I was questioning the need for an alias **for this specific statement**, since there is only one table referenced. You _can_ use an alias in this case, but why is it useful? Compatibility with other (more complex) delete statements, maybe, but seems a stretch to me. – Aaron Bertrand Dec 19 '20 at 00:04
  • @AaronBertrand depends on your environment. I need it in PL/SQL Developer. – Jamie Dec 21 '20 at 19:10
  • @Jamie Sounds like an issue you should raise with your vendor, instead of changing your coding pattern to suit its limitations. – Aaron Bertrand Dec 23 '20 at 15:48
  • 1
    @Mike sounds like a new question since your requirements are different and you want it to apply to more than one platform. – Aaron Bertrand Sep 15 '21 at 16:31
  • I would like to add my SQL case for using aliases in delete statements, because it is indeed required to use them when having an `EXISTS` query: `DELETE p FROM products AS p WHERE NOT EXISTS (SELECT * FROM tempProductsTable tp WHERE p.productID = tp.productID AND p.organizationID = tp.organizationID)` – Koenman Jan 19 '23 at 12:56
  • @Koenman I never said you don’t need aliases in delete statements, I use them all the time. My answer says “for this specific statement.” – Aaron Bertrand Jan 19 '23 at 13:04
  • `though I fail to see the point of aliasing for this specific statement` - well, in my case, inside `in` clause i used a recursive `CTE` on **this very table** i was deleting from to build a sub-tree on a self-referentiual tree #-table, that i wanted to be removed from the total output. Now, *technically* aliasing is still not required there, but i feel so much more safe when i do use it... It was a bummer MS SQL did not support the SQL standard aliasing. On a second thought though, i decided to stay with SQL-standard non-aliasing rather than lock my app into a non-SQL MS-specific lingo – Arioch 'The Apr 25 '23 at 13:24
  • @Mike using CTE, common table expressions ? – Arioch 'The Apr 25 '23 at 13:26
  • @Arioch'The When you see “this specific statement” then say you have this very different other statement, ok, cool, but what’s your question or objection to what I said? – Aaron Bertrand Apr 25 '23 at 13:38
  • I mean, I even put those words in bold because people somehow infer I declared _aliasing is always bad_. I was merely suggesting that, for a simple, single-table delete with no joins or CTEs or external references, the alias isn’t necessary. I’m not denouncing a religion. – Aaron Bertrand Apr 25 '23 at 13:45
97

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

DELETE f FROM foods f WHERE (f.name IN ('chickens', 'rabbits'))
usr
  • 168,620
  • 35
  • 240
  • 369
  • 1
    @Ricardo only difference is the schema. But the time suggests both were posted at the same time. – Mukus Mar 14 '14 at 05:37
  • So what is wrong with delete M from MailList as M where exists (select * from MailList as N where N.oldemail = M.email); – Tuntable May 10 '21 at 05:16
  • @Tuntable I don't think there is anything wrong with that. Not sure how this relates to the question, though, because your query has this particular join condition which is different from what is asked here. If you elaborate I can answer. – usr May 19 '21 at 09:19