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?
This question is related to
mysql
sql
mysql-error-1064
sql-delete
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]
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.
I found this article which showing you how to delete data from multiple tables by using MySQL DELETE JOIN statement with good explanation.
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
The syntax looks right to me ... try to change it to use INNER JOIN
...
Have a look at this.
Use this
DELETE FROM `articles`, `comments`
USING `articles`,`comments`
WHERE `comments`.`article_id` = `articles`.`id` AND `articles`.`id` = 4
or
DELETE `articles`, `comments`
FROM `articles`, `comments`
WHERE `comments`.`article_id` = `articles`.`id` AND `articles`.`id` = 4
Source: Stackoverflow.com