How to delete from multiple tables in MySQL?
Use a JOIN
in the DELETE
statement.
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 delete only from pets_activities
See this.
For single table deletes, yet with referential integrity, there are other ways of doing with EXISTS
, NOT EXISTS
, IN
, NOT IN
and etc. But the one above where you specify from which tables to delete with an alias before the FROM
clause can get you out of a few pretty tight spots more easily. I tend to reach out to an EXISTS
in 99% of the cases and then there is the 1% where this MySQL syntax takes the day.
Since this appears to be a simple parent/child relationship between pets
and pets_activities
, you would be better off creating your foreign key constraint with a deleting cascade.
That way, when a pets
row is deleted, the pets_activities
rows associated with it are automatically deleted as well.
Then your query becomes a simple:
delete from `pets`
where `order` > :order
and `pet_id` = :pet_id