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
from db1.apples as a
inner join db2.oranges as o on o.price = a.price;
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
from db1.apples as a
inner join db2.oranges as o on o.price = a.price;
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
from db1.apples as a
inner join db2.oranges as o on o.price = a.price;
… but see my SQL coding standards for why I always include the optional but should-be-mandatory AS keyword.
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.
Technorati Tags:No Tags
I tried your method, however it doesn’t seem to work with multiple tables in the delete statement using jdbc / connection j
delete tag_item as ti, list_item as li from tag_item as ti, list_item as li where ti.li_id = li.id and li.datum_id = ? and li.user_id = ?;I keep getting Unknown table ‘li’ in MULTI DELETE
On mysql 5.0.2.2
Right, you also need to fully qualify the tables with a database name too. I mentioned that in the first article, but left it out of this one.
So your code should say,
Will this work with db2 and MySQL tables? My query fails with the same error:
How should I change this query? I played with quite a few combinations but still can’t get by this error.
Thanks
This technique is specifically about cross-database joins on MySQL, which has some very annoying quirks. It doesn’t apply to selects. I’ve never seen syntax quite like yours — you might not have valid SQL there. Also, you seem to be talking about cross-server deletes; this is only cross-database, not cross-server (a single server/instance may have many databases).
You should probably seek help on #mysql or a mailing list. There are lots of friendly folks there who can help you more quickly than I’ll be able to in these comments ;-)
Hi,
Can you please tell me how can write CROSS-SERVER queries in mysql. I want to join tables from two different mysql servers for .net desktop application.
Thanks
Anz
You can’t do it. You may want to look into the FEDERATED storage engine. For more help, try the #mysql IRC channel.
Thank you so much. You saved my neck :D