Delete rows from multiple tables at once

Question

I am trying to combine 2 queries into one such

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

IN

 $result=$db->query ("DELETE FROM menu AS m, pages AS p WHERE m.name = 'new' AND p.title='new'") or die($db->error); 

But DB gives a syntax error. What's wrong?

Answer 1

You may well delete from several tables in one of the statements with MySQL . Your requirements will work using 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 is one example with these examples: this requires the existence of both values.

There should be more ways to get the desired result, I think, without this restriction (using other types of JOIN , I would have thought), but I could not figure out how this should work.

If the rows you want to delete are somehow connected to each other using foreign keys (and you are not using foreign key constraints with InnoDB tables), it should be even easier 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. First experiment with the test environment, as I’m not sure that the consequences are 100% accurate, to be honest; I just wanted to give a hint that several tables are deleted at once.

Answer 2

DELETE operations must be performed one table at a time. There is no way to combine them, as you are trying to do. Depending on what you are trying to execute, you can use a transaction for two operations.

Answer 3

If the tables are associated with a foreign key, you can look at ON DELETE CASCADE . Basically, this is that if you delete a row that other tables are bound to, it also deletes rows in related tables.

• MySQL
• PostgreSQL