Delete rows from multiple tables at once

I'm trying to combine 2 queries into one like this

$result=$db->query ("DELETE FROM menu WHERE name = '$new'") or die($db->error);
$result=$db->query ("DELETE FROM pages WHERE title = 'new'") or die($db->error);

Into

$result=$db->query ("DELETE FROM menu AS m, pages AS p WHERE m.na开发者_如何学JAVAme = 'new' AND p.title='new'") or die($db->error);

But DB gives syntax error. What's wrong?

DELETE operations have to be done one table at a time. There's no way to combine them as you are trying to do. Depending on what you're trying to accomplish, you may want to use a transaction for the two operations.

You sure well can delete from multiple tables in one statement with MySQL. Your requirements would work using something like the following query:

DELETE
    menu, pages
FROM
    menu JOIN pages
WHERE
    menu.name = 'some name' AND
    pages.title = 'some title'

Or:

DELETE FROM
    menu, pages
USING
    menu JOIN pages
WHERE
    menu.name = 'some name' AND
    pages.title = 'some title'

There's one catch with these examples though: both values must exist for this to work.

There should be more ways to get the desired result I think, without this constraint (using other types of JOINs I would think) but I haven't been able to figure out how that should work.

If the rows you want to delete are somehow linked to each other with foreign keys (and you are not using foreign key constraints with InnoDB tables), it should be even more easy to do what you want. Something like this should work then:

DELETE
    menu, pages
FROM
    menu LEFT JOIN pages
    ON menu.pageId = page.id # using imagined columns here
WHERE # this where clause might be redundant then
    menu.name = 'some name' AND
    pages.title = 'some title'

Be careful with these examples though. Experiment on a test environment first, as I'm not sure 100% of the exact implications to be honest; I just wanted to provide a hint for the fact that deleting from multiple tables at once is possible.

If the tables are related with a foreign key, you might want to look into ON DELETE CASCADE. Basically what it does is if you delete a row that other tables are foreign-keyed to, it deletes the rows in the related tables too.

• MySQL
• PostgreSQL