Bug #12811 DELETE doesn't support aliases
Submitted: 25 Aug 2005 17:08 Modified: 27 Nov 2008 18:50
Reporter: Jeremy Cole (Basic Quality Contributor) (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server Severity:S4 (Feature request)
Version:4.1.x, 5.0.11 OS:Any (any)
Assigned to: CPU Architecture:Any

[25 Aug 2005 17:08] Jeremy Cole
Delete doesn't support aliases.  This makes it difficult to write multi-table deletes especially.  Oracle supports aliases with DELETE, why not MySQL?  What does the SQL standard say about DELETE with aliases?

How to repeat:
create table s (i int);
delete from s as t;
[25 Aug 2005 17:13] MySQL Verification Team
Sounds as a feature request.
[25 Aug 2005 17:36] Valeriy Kravchuk
Aliases are not supported in 5.0.11 too.

Alias (correlation name) support in DELETE statement is included as option into the draft SQL 2003 standard:

"<delete statement: searched> ::=
DELETE FROM <target table> [ [ AS ] <correlation name> ]
[ WHERE <search condition> ]"

so, it at least a feature request.
[25 Aug 2005 22:25] Harrison Fisk
You can alias DELETE's with the multi-table join syntax.

For example, the following all work on 4.1:

mysql> DELETE FROM c USING Country AS c, City AS ci WHERE 0;
Query OK, 0 rows affected (0.20 sec)

mysql> DELETE FROM c USING Country AS c WHERE 0;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE c FROM Country AS c WHERE 0;
[25 Sep 2005 4:26] Stefan Proels
I'd like to endorse this feature request. It is especially useful with subqueries in the where clause, for example

DELETE FROM Foo f WHERE EXISTS (SELECT 1 FROM Bar b WHERE f.x = b.x AND f.y = b.y)
[17 Jun 2009 22:59] Chris Khan
It would also help code-readability, syntax-predictability, and generated-code-compatibility.
[17 Jun 2009 23:03] Chris Khan
Regarding the multi-table syntax to enable the use of an alias: it seems to negate the use of order by and limit clauses.  Choosing between those features is not fun.