How to delete from multiple tables in MySQL?

alex_31671 0
Viewed 232563

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?

mysql-error-1064 sql-delete mysql sql
7 Answers

To anyone reading this in 2017, this is how I've done 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

Generally, to delete rows from multiple tables, the syntax I follow is given below. The solution is based on an assumption that there is some relation between the two tables.

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

I found this article which showing you how to delete data from multiple tables by using MySQL DELETE JOIN statement with good explanation.

enter image description here

user_66312800
Related