Поиск всех путей от корня до одного конкретного листа

Используйте JOIN в инструкции 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

В качестве альтернативы вы можете использовать ...

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

... для удаления только из pets_activities

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

Для удаления одной таблицы, но с ссылочная целостность, есть другие способы делать с EXISTS, НЕ СУЩЕСТВУЕТ, IN, NOT IN и т. д. Но выше, где вы указываете, из каких таблиц, которые нужно удалить с помощью псевдонима перед предложением FROM, может вытащить вас из нескольких довольно жестких пятна легче. Я имею тенденцию обращаться к EXISTS в 99% случаев, а затем есть 1%, где этот синтаксис MySQL занимает день.