How to delete from multiple tables in MySQL?

Question

I am trying to delete from multiple tables at once. I did some research and came up with this

DELETE FROM `pets` p, `pets_activities` pa WHERE p.`order` > :order AND p.`pet_id` = :pet_id AND pa.`id` = p.`pet_id` 

However i get this error

Uncaught Database_Exception [1064]: You have an error in the SQL syntax; check the manual for your version of MySQL server for the correct syntax to use next to p, pets_activities pa ...

I've never done a cross table before, so I'm inexperienced and stuck so far!

What am I doing wrong?

Answer 1

Use the JOIN in the DELETE .

 DELETE p, pa FROM pets p JOIN pets_activities pa ON pa.id = p.pet_id WHERE p.order > :order AND p.pet_id = :pet_id 

Alternatively you can use ...

 DELETE pa FROM pets_activities pa JOIN pets p ON pa.id = p.pet_id WHERE p.order > :order AND p.pet_id = :pet_id 

... to remove only from pets_activities

See http://dev.mysql.com/doc/refman/5.0/en/delete.html

For single table deletions, but with referential integrity, there are other ways to run with EXISTS, DO NOT EXIST, IN, NOT IN, etc. But above, where you specify from which tables to delete using an alias before FROM, a sentence can easily save you from several rather bottlenecks. I tend to access EXISTS 99% of the time, and then there is 1% where the MySQL syntax takes a day.

Answer 2

Since this seems to be a simple parent / child relationship between pets and pets_activities , you would be better off creating a foreign key constraint by removing the cascade.

Thus, when the pets line is deleted, the associated pets_activities line pets_activities also automatically deleted.

Then your request will become simple:

 delete from `pets` where `order` > :order and `pet_id` = :pet_id 

Answer 3

Use this

 DELETE FROM `articles`, `comments` USING `articles`,`comments` WHERE `comments`.`article_id` = `articles`.`id` AND `articles`.`id` = 4 

or

 DELETE `articles`, `comments` FROM `articles`, `comments` WHERE `comments`.`article_id` = `articles`.`id` AND `articles`.`id` = 4 

Answer 4

I do not have a mysql database to test at the moment, but have you tried to specify what needs to be removed before the from clause? For example:

 DELETE p, pa FROM `pets` p, `pets_activities` pa WHERE p.`order` > :order AND p.`pet_id` = :pet_id AND pa.`id` = p.`pet_id` 

I think the syntax you use is limited to newer versions of mysql.

Answer 5

The syntax looks right for me ... try changing it to use INNER JOIN ...

Take a look at this: http://www.electrictoolbox.com/article/mysql/cross-table-delete/

Answer 6

For everyone who reads this in 2017, I did something similar.

 DELETE pets, pets_activities FROM pets inner join pets_activities on pets_activities.id = pets.id WHERE pets.`order` > :order AND pets.`pet_id` = :pet_id 

Typically, the following syntax is used to delete rows from multiple tables. The solution is based on the assumption that there is some relationship between the two tables.

 DELETE table1, table2 FROM table1 inner join table2 on table2.id = table1.id WHERE [conditions]