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

sql sql-server sql-server-ce

75,416

Solution 1

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

Solution 2

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

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

75,416

Ricardo Altamirano

Originally from Nicaragua, educated in Edinburgh and the USA, and now living primarily in London. Useful questions and answers Stack Overflow Debugging CREATE TABLE statements Logging in Python Simple string formatting in Python Reference types in C# String compression in C# using Gzip Meta Stack Overflow Book recommendation questions Answering old questions with a solution in the comments IT Security Cryptographically secure random strings in PHP LaTeX Fitting a table on a page through rotation StackExchange Flair

Updated on March 02, 2022

Comments

Ricardo Altamirano over 2 years
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 about 12 years
  
  @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.
- Adir D about 12 years
  
  No worries. Just trying to make it clear for other readers.
- Adir D about 12 years
  
  I do agree with you by the way that the syntax variations between different commands is a little unintuitive at times.
- Daniel Neel over 9 years
  
  Here's the same question, but for UPDATE statements: stackoverflow.com/questions/31551/…
Ricardo Altamirano about 12 years

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 over 10 years

+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.
Mukus over 10 years

@Ricardo only difference is the schema. But the time suggests both were posted at the same time.
Alex about 9 years

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.
Abacus over 8 years

Strange syntax, but unfortunately this is required if deleting from a table variable -- see stackoverflow.com/questions/597513/…
JohnLBevan almost 8 years

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)
BrianK almost 8 years

I needed an alias, found this post. Thanks!
Andrew Jens almost 7 years

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
Jamie over 3 years

Aliasing allows you to use autocomplete for column names when you're writing the statement, which is convenient for ad-hoc deletes.
Adir D over 3 years

@Jamie You don't need an alias for autocomplete to work, particularly when the statement (like the one in the question) only references a single table.
Adir D over 3 years

@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.
Jamie over 3 years

@AaronBertrand depends on your environment. I need it in PL/SQL Developer.
Adir D over 3 years

@Jamie Sounds like an issue you should raise with your vendor, instead of changing your coding pattern to suit its limitations.
Mike over 3 years

Is there a better ANSI compliant way to perform a DELETE query that uses a subquery that selects from the same table in the where clause? Perhaps a JOIN?
Tuntable about 3 years

So what is wrong with delete M from MailList as M where exists (select * from MailList as N where N.oldemail = M.email);
usr about 3 years

@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.
Adir D almost 3 years

@Mike sounds like a new question since your requirements are different and you want it to apply to more than one platform.