How to delete from multiple tables in MySQL?

MysqlSqlMysql Error-1064Sql Delete

Mysql Problem Overview

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 Solutions

Solution 1 - Mysql

Use a JOIN in the DELETE statement.

DELETE p, pa
      FROM pets p
      JOIN pets_activities pa ON = p.pet_id
     WHERE p.order > :order
       AND p.pet_id = :pet_id

Alternatively you can use...

      FROM pets_activities pa
      JOIN pets p ON = p.pet_id
 WHERE p.order > :order
   AND p.pet_id = :pet_id delete only from pets_activities

See this.

For single table deletes, yet with referential integrity, there are other ways of doing with EXISTS, NOT EXISTS, IN, NOT IN and etc. But the one above where you specify from which tables to delete with an alias before the FROM clause can get you out of a few pretty tight spots more easily. I tend to reach out to an EXISTS in 99% of the cases and then there is the 1% where this MySQL syntax takes the day.

Solution 2 - Mysql

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

Solution 3 - Mysql

Use this

DELETE FROM `articles`, `comments` 
USING `articles`,`comments` 
WHERE `comments`.`article_id` = `articles`.`id` AND `articles`.`id` = 4


DELETE `articles`, `comments` 
FROM `articles`, `comments` 
WHERE `comments`.`article_id` = `articles`.`id` AND `articles`.`id` = 4

Solution 4 - Mysql

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.

Solution 5 - Mysql

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

Have a look at this.

Solution 6 - Mysql

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 = 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 =
WHERE [conditions]

Solution 7 - Mysql

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


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionalexView Question on Stackoverflow
Solution 1 - MysqlcadmanView Answer on Stackoverflow
Solution 2 - MysqlpaxdiabloView Answer on Stackoverflow
Solution 3 - MysqlRN KushwahaView Answer on Stackoverflow
Solution 4 - MysqlBrandon HorsleyView Answer on Stackoverflow
Solution 5 - MysqlAndre GalloView Answer on Stackoverflow
Solution 6 - MysqlKalesh KaladharanView Answer on Stackoverflow
Solution 7 - MysqlRavi MakwanaView Answer on Stackoverflow