Search code examples
mysqlsqljoinsubquerysql-delete

Delete from multiple tables in one statement

Using MySQL Im trying to delete multiple records at once from multiple tables. Initially I thought doing something like:

DELETE t1, t2
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.table1_id
JOIN table3 t3 ON t1.id = t3.table1_id
WHERE t1.id IN (?,?,?,?);

However, if there are no existing record within table2, would I change the JOIN to a LEFT JOIN? Also, would doing this cause latency if I were only deleting two or three records across eight tables(2x2x2x2x2x2x2x2)?

Solution

  • Yes, changing the join on table2 to left join would do what you want. Rows in table1 that belong to the list and to table3 will all get deleted, regardless whether they also exist in table2. In the meantime, possible matching rows in table2 will be deleted too.

    delete t1, t2
from table1 t1
left join table2 t2 on t1.id = t2.table1_id
inner join table3 t3 on t1.id = t3.table1_id
where t1.id in (?, ?, ?, ?);

    I would suggest rewriting the join on table3 to an exists condition instead. This makes the intent of the query more obvious, and might perform better, especially if you have an index on table3(table1_id):

    delete t1, t2
from table1 t1
left join table2 t2 on t1.id = t2.table1_id
where 
    t1.id in (?, ?, ?, ?)
    and exists (select 1 from table3 t3 where t3.table1_id = t1.id)