Delete from multiple tables in one statement
P粉716228245
P粉716228245 2023-08-18 10:28:49
0
1
575
<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>
P粉716228245
P粉716228245

reply all(1)
P粉118698740

Yes, changing the join on table2 to left join will achieve the effect you want. Rows in table1 that belong to the list and table3 will be deleted, regardless of whether they also exist in table2. At the same time, possible matching lines will also be deleted.

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 recommend rewriting the join on table3 to the exists condition. This makes the intent of the query clearer and may perform better, especially if there is 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)
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template