Delete from multiple MySQL tables
I have just spent the last half hour scratching my head over how to delete from 2 tables, joined by a unique ID. For some reason, MySQL kept spitting out: Unknown table ‘myTable’ in MULTI DELETE. Many visits to the MySQL manual still made me scratching my head (Yes, looking back on the manual makes me hit my head for not noticing the “Note” about a third of the way down the page).
The syntax
The MySQL manual says the syntax is:
- DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
- tbl_name[.*] [, tbl_name[.*]] ...
- FROM table_references
- [WHERE where_condition]
What the MySQL manual really means
I’m not exactly a God at SQL—I fully acknowledge that. I do ask questions from time-to-time, so the manual should be geared towards people like me. Shouldn’t it?
I fell into the trap by giving my tables aliases in the FROM table references bit, so my SQL looked like this:
- DELETE `table1`,
- `table2`
- FROM table1 t1,
- table2 t2
- WHERE t1.row1 = t2.row1
Now, you can imagine how much sense the MySQL error was giving me. I knew the 2 tables existed, so it wasn’t “unknown” in that sense. Only upon scouring the Internet did I suddenly have a hand-to-forhead moment. When defining the “DELETE” tables, if you have used aliance in the table reference section then you need to use those instead! My query should have looked like this, which it now does and works spiffingly:
- DELETE t1,
- t2
- FROM table1 t1,
- table2 t2
- WHERE t1.row1 = t2.row1
I hope that saves someone some trouble.
1 George Swan wrote
24th June, 2008
Don’t think I have ever needed to remove rows from more than one table at the same time, but that is some weird syntax but can see why they have done it—as the manual says you might want to only search one table but not delete from it.