Subscribe to PostsRecently was asked a question on how to delete records from multiple tables with a single MySQL query. I’ll give an example below with one caveat. If your tables are INNODB and you have foreign keys then this query will fail. For INNODB you should just do a single delete and rely on the ON DELETE capabilities to handle any other deletes.
Anyways, so on to the query. Say you have 3 tables (table1, table2, and table3). Each of them have an entry for a variable that we’ll call $id and you want to delete from all 3 tables at once. Here you go:
delete t1, t2, t3 from table1 as t1 left join table2 as t2 on t2.id = t1.id left join table3 as t3 on t3.id = t1.id where t1.id = '$id'
Leave a reply
Randy
October 26th, 2009 at 17:35
I have been trying to figure out how to delete from 4 tables all containing related info about the main table and this worked perfectly. Thank you so much!
RickB
November 18th, 2009 at 05:47
Glad it helped!
Bruce
November 17th, 2009 at 10:05
Great post. This was exactly what I was looking for and it worked perfectly. You probably saved me an hour of frustration. Thanks!
RickB
November 18th, 2009 at 05:48
Thanks for the feedback, glad it helped you out.