How to write multi-table, cross-database deletes with aliases in MySQLMon, Aug 7, 2006 in Databases
In an earlier article I explored the finer points of multi-table, cross-database deletes in MySQL, but I didn’t get it quite right. This article explains the subtleties I’ve discovered since then. This is more complex than the MySQL manual documents.
In the earlier article, I wrote
The solution is not to alias the tables. It’s less convenient, but it’s the only thing to do sometimes.
Now I find it is possible to alias the tables, with certain restrictions. Apparently the way you name the target of the
DELETE, and the way you first declare the aliases in the
FROM clause, must match exactly. Then it’s possible to do these deletes without fully qualifying the tables everywhere, and without having a current database selected.
For example, suppose you want to delete rows from
apples, when they match in a join to
oranges. The tables are in different databases. The following will not work:
delete apples ERROR 1109 (42S02): Unknown table 'apples' in MULTI DELETE
The error is because
apples is aliased to
a. If you try to name
a in the
DELETE clause, a similar error happens. However, the following will work:
delete db1.apples as a
Now you’ve named the tables identically in both places, so it works. It’s as though you have to re-alias the table in the
DELETE clause. The optional
AS keyword may be omitted in one or both places you declare an alias, so even this form will work:
delete db1.apples a
… but see my SQL coding standards for why I always include the optional but should-be-mandatory
With these requirements met, you can use aliases in the rest of the (possibly complex) query, which is much nicer than writing out fully qualified names everywhere.
I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and lots of open-source software for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB. More about me.