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 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.