MySQL: DELETE across multiple tables using JOIN.
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.
November 1st, 2007 at 12:33 pm
I have never managed to get more then 1 join ¬¬ how dit you get multiple joins in 1 query
you bastard ^^
January 19th, 2008 at 10:03 am
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.
May 29th, 2008 at 4:34 am
Ahhhhh, great!
Thank you very much, that mysql manual is not really helpful in that situation!
June 15th, 2008 at 2:35 am
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!!!
June 24th, 2008 at 6:11 pm
Thank you…this little gem has saved me hours of work experimenting to see if it could be done.
June 30th, 2008 at 11:54 am
Really It’s helpful and useful query.
July 15th, 2008 at 3:25 pm
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.
July 15th, 2008 at 3:37 pm
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.”
September 24th, 2008 at 12:28 pm
This query is fantastic, but it won’t work if there are foreign key constraints. What’s your suggestion to do with constraints?