MySQL DELETE Statement Alias Support Differences Between 5.7 and 8.0

Background

While trying to delete unnecessary data according to business requirements, a DELETE statement on MySQL 5.7.34 produced a syntax error:

mysql> delete from test1 t1 where not exists (select 1 from test2 t2 where t1.id=t2.id);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1 where not exists (select 1 from test2 t2 where t1.id=t2.id)' at line 1

The error was likely caused by a misspelled keyword or the presence of Chinese punctuation. After ruling out those causes, the author questioned whether the NOT EXISTS construct is unsupported in DELETE statements.

Analysis

The DELETE syntax for MySQL 5.7 single‑table format is:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

This format does not show the use of table aliases, suggesting that aliases may be the source of the error. Testing the same statement without the alias succeeds:

mysql> delete from test1 where not exists (select 1 from test2 where test1.id=test2.id);
Query OK, 1 row affected (0.00 sec)

Further comparison with MySQL 8.0 reveals that the single‑table DELETE syntax was extended to allow an optional alias:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

For multi‑table DELETE, both 5.7 and 8.0 support aliases via the table_references clause.

For consistency with the SQL standard and other RDBMS, table aliases are now supported in single-table as well as multi-table DELETE statements. (Bug #27455809)

Conclusion

In MySQL 5.7, table aliases cannot be used in single‑table DELETE statements, though they are allowed in multi‑table DELETE.

Starting with MySQL 8.0.16, aliases are supported in both single‑table and multi‑table DELETE statements.