How to write multi-table, cross-database deletes with aliases in MySQL at Xaprb

How to write multi-table, cross-database deletes with aliases in MySQL

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.

Further Reading:

Written by Xaprb

August 7th, 2006 at 4:00 pm

Posted in Uncategorized

10 Responses to 'How to write multi-table, cross-database deletes with aliases in MySQL'

Subscribe to comments with RSS

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
Patrick

14 Aug 06 at 2:59 pm
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,
```
delete somedb.tag_item as ti, somedb.list_item as li...
```
Xaprb

14 Aug 06 at 3:32 pm

Will this work with db2 and MySQL tables? My query fails with the same error:

SELECT incident.guid, incident.update_date, task1.short_description FROM (
DB2_A.incident incident  LEFT JOIN MySQL_B.task task1 ON incident.incident_id = task1.id
)  WHERE incident.guid = x'FF0BFBDD917EEB7D55B545FBD889FEEB'

How should I change this query? I played with quite a few combinations but still can’t get by this error.

Thanks

BermG

9 Jan 07 at 3:15 pm

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 ;-)

Xaprb

9 Jan 07 at 3:52 pm
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

Anz

11 Apr 07 at 6:32 am
You can’t do it. You may want to look into the FEDERATED storage engine. For more help, try the #mysql IRC channel.

Xaprb

11 Apr 07 at 2:21 pm
Thank you so much. You saved my neck :D

Sprok

16 Apr 07 at 2:41 am
thanks man. this issue bit me this morning. thanks for posting the fix. cheers — alan

Alan

11 Jan 09 at 12:26 pm
This is a great write-up. I didn’t realize I needed to fully qualify the database and table names. You saved me a load of time. Thanks!

Dave Rowe

8 Dec 09 at 2:40 pm
Note that this was fixed in 5.5.3

http://dev.mysql.com/doc/refman/5.5/en/news-5-5-3.html

I’ve got a 5.1 master replicating to a 5.5 slave, this is causing a few headaches. I think the only way to have a multi-table delete statement that works across both versions is to make sure you don’t alias the tables at all.

Paul

Paul Keenan

1 Nov 11 at 11:42 am