MySQL Bugs: #12811: DELETE doesn't support aliases

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:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.1.x, 5.0.11	OS:	Any (any)
Assigned to:		CPU Architecture:	Any

Description:
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;

Sounds as a feature request.

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.

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;

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)

It would also help code-readability, syntax-predictability, and generated-code-compatibility.

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.

This was resolved in MySQL 8.0.16. References:
- Bug #89410
- Bug #27455809
- Release Notes (https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-16.html)
- MySQL manual (https://dev.mysql.com/doc/refman/8.0/en/delete.html)