How to delete from multiple tables in MySQL

Question

I am trying to delete from a few tables at once. I've done a bit of 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 am getting this error

Uncaught Database_Exception [ 1064 ]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'p, pets_activities pa...

What am I doing wrong?

Answer 1

Hello @kartik,

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

Hope it helps!!
Thank You!