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...

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

What am I doing wrong?

Thanks

Answer 1

You should not delete from multiple tables in one query.

DELETE FROM pets WHERE pet_id = :pet_id;
DELETE FROM pets_activities WHERE pet_id = :pet_id;

I'm not sure why you're deleting by pet_id and order. It seems strange to me, and I'd wonder what your reasoning is. If you wanted to delete by something else, you should do this:

DELETE FROM pets WHERE order > :order;
DELETE FROM pets_activities WHERE pet_id Not In (SELECT pet_id FROM pets);

And it's also ambiguous to name a column ID in the pets_activities table and expect anyone else to realize that it's supposed to be the pet_id from the pets table. You should name it pet_id instead for clarity.

Answer 2

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

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

Answer 3

Since this appears to be a simple parent/child relationship between pets and pets_activities, you would be better off creating you 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

Answer 4

I don't have a mysql database to test on at the moment, but have you tried specifying what to delete prior to 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 used is limited to newer versions of mysql.