Delete from multiple MySQL tables June 23rd, 2008

 

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.

 

Comments so far

George Swan
July 2009

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.

Lig
July 2009

As George said, it is so you can select, but not delete, from some tables. The syntax may look a little strange for first timers though from the DELETE FROM syntax.