Blog

Delete from mutliple tables with a single query

Recently 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:

[bash]
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’
[/bash]

Tags:

2 Responses to “Delete from mutliple tables with a single query”

  1. SD says:

    Whoa. i’ve been doing it the ‘one at a time’ way for quite awhile in ubb.threads. This is perty handy.

    I learned something today! ;)

Leave a Reply