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

sql sql-server sql-server-ce
Ricardo Altamirano picture Ricardo Altamirano · Jun 12, 2012 · Viewed 61.9k times · Source

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

Answer

Aaron Bertrand picture Aaron Bertrand · Jun 12, 2012

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

Related questions

How to identify whether the table has identity column

I want to find out whether the table has an identity column or not. Table is unknown to me. I have not done the structure of the table. Using Query? I am using Sql Server Compact Edition.

Read More
SQL Server queries case sensitivity

I have this database: abcDEF ABCdef abcdef if I write: select * from MyTbl where A='ABCdef' how to get: ABCdef and how to get: abcDEF ABCdef abcdef Thanks in advance forgot to write - sqlCE

Read More
Determine if table exists in SQL Server CE?

I know this is similar to this question, but I'm using SQL Server CE 3.5 with a WinForms project in C#. How can I determine whether a table exists? I know the IF keyword is not supported, though EXISTS is. Does …

Read More