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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   gt   order       and  pet id     pet id

User · Answer

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   gt   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 · Answer

The syntax looks right to me     try to change it to use INNER JOIN       Have a look at this

User · Answer

Use a JOIN in the DELETE statement   DELETE p  pa       FROM pets p       JOIN pets activities pa ON pa id   p pet id      WHERE p order  gt   order        AND p pet id    pet id   Alternatively you can use     DELETE pa       FROM pets activities pa       JOIN pets p ON pa id   p pet id  WHERE p order  gt   order    AND p pet id    pet id      to 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

[mysql] How to delete from multiple tables in MySQL?

Examples related to mysql

Examples related to sql

Examples related to mysql-error-1064

Examples related to sql-delete