Delete from multiple tables in one statement
P粉716228245
2023-08-18 10:28:49
<p>Using MySQL, I am trying to delete multiple records from multiple tables at once.
Initially I thought I could do this: </p>
<pre class="brush:php;toolbar:false;">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 (?,?,?,?);</pre>
<p>However, if there are no existing records in table2, should I change <strong>JOIN</strong> to <strong>LEFT JOIN</strong>? Also, if I delete only two or three records from the eight tables (2x2x2x2x2x2x2x2), will this cause a delay? </p>
Yes, changing the join on
table2toleft joinwill achieve the effect you want. Rows intable1that belong to the list andtable3will be deleted, regardless of whether they also exist intable2. At the same time, possible matching lines will also be deleted.I recommend rewriting the
joinontable3to theexistscondition. This makes the intent of the query clearer and may perform better, especially if there is an index ontable3(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)