Professional Freelance Web Developer
CodeIgniter Activist, Caffeine Junkie

Elliot Haughin

MySQL: DELETE across multiple tables using JOIN.

1st November 2007

Ever had a situation where you’ve wanted to say delete rows from lots of tables in your database that can all be associated with some form of join?
Well, after a few minutes of playing around, I’ve found a nice way to do this in just 1 query:

DELETE u, up, upc, ut, utp
FROM users AS u
LEFT JOIN users_photos AS up ON up.user_id = u.id
LEFT JOIN users_photos_comments AS upc ON upc.photo_id = up.id
LEFT JOIN users_topics AS ut ON ut.user_id = u.id
LEFT JOIN users_topic_posts AS utp ON utp.topic_id = ut.id
WHERE u.id = 12

The code is pretty straight forward, and simple to use.

  • swetha
    Excellent this works on my db by changing the table name.That's it and thanks for the post!!!!!!!!!
  • Thanks for this post! It ended up helping me figure out a query I've been working awhile on.
  • kahtrina
    i am deleting a row in a detail jqgrid but when i click the refresh button, the one i deleted comes back. i have to delete it from two tables that are joined. how can i delete it??? here is my thread for you to see: http://codeigniter.com/forums/viewthread/145869/

    hope you can help me,
    thanks in advance
  • GJ
    thanks buddy its working.
    hei i also need another help from u. will u plz tell me how to insert values in multiple tables using only one query??will u plz give me an example of it??
  • Thanks good functionality.....

    Just make it more powerful with the Foreign Key Constraint.

    even though good for newbee....

    - Yogi Ghorecha
  • Beauford
    Doesn't work. I have searched high and low for a way to do this, to no avail. Every code I find just does not work. Mysql v5+.

    Any ideas:

    Delete from bunnylist AS b LEFT JOIN userstats AS u WHERE u.strength > 25000 || u.guard > 25000;

    Last thing I tried.

    THX
  • Jason
    It works Hurrahhh!
  • swarupa
    datz kool ... thanks buddy !
  • Neelima
    Thank you very much ....its working yaar...realy very good code....thnks again
  • waleed
    thank you very much with your example you made this part so clear
  • Wohoo !! It indeed works ! Thanks for that piece of code.

    Do you have any idea if UPDATEs can be performed across multiple tables using a similar query structure? Tried a lot of combination but haven't managed to figure it out yet.

    Cheers,
    m^e
  • David Stevanus
    thanks, it works.....i have 4 tables, and i used it to delete multiple tables....good job
  • This query is fantastic, but it won't work if there are foreign key constraints. What's your suggestion to do with constraints?
  • Caleb
    Hmmm.... it looks like MySQL may not delete in the correct parent-child order specified by your foreign keys:


    From http://dev.mysql.com/doc/refman/5.1/en/delete.html:
    "If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, you should delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly."
  • Caleb
    What's the deletion order? Does it delete in the order the tables were specified in the FROM stanza? Or does MySQL figure it out itself? If you have foreign key dependencies deletion order is important.
  • krishna
    Really It's helpful and useful query.
  • HardcoreProgrammer
    Thank you...this little gem has saved me hours of work experimenting to see if it could be done.
  • Spacebiker
    Than u VERY VERY VERY VERY VERY VERY VERY VERY VERY VERY VERY VERY VERY VERY VERY VERY VERY VERY VERY VERY VERY VEEEEEEEEEEEEEERY MUCH!!!!
    U're genius!!!
  • justy
    Ahhhhh, great! :D
    Thank you very much, that mysql manual is not really helpful in that situation!
  • Hi,
    that's what i was lookin' for. Unfortunately it doesn't works on older mysql (3.23x) as i have. I'm getting mad to find a workaround... but i'm afraid the older versions cannot permit left joins within delete queries. :(
  • Hostingx
    I have never managed to get more then 1 join ¬¬ how dit you get multiple joins in 1 query :P you bastard ^^
blog comments powered by Disqus

Boring Stuff

Design © copyright Elliot Haughin 2009

Content published here are copyright their respective owners.

You cannot copy content from this site, either in English or translated to another language.

Keep Subscribed

Theres lots of ways for you to keep up with me on the web.

Please Note

Information given out on this blog should only be used as a guideline. I hold no liability for any code I write.

Always consult a professional before acting on this guidance.