[Solved] MySQL Delete from multiple tables in one query

I have these tables and I’m trying to run a multiple delete query that would delete all the products and tasks when deleting a repair record.
Note that the linking tables have ON DELETE CASCADE constraints.

Now the problem that I am having is that when I run this query it only deletes the repair, taskRepair, (1 task record but it has 3 in total), and the products associated with the deleted task.

DELETE bp, t, r
FROM repair AS r
LEFT JOIN taskRepair AS tr ON r.repairID = tr.repairID
INNER JOIN task AS t ON t.taskID = tr.taskID
LEFT JOIN boughtProductTask AS bpt ON bpt.taskID = t.taskID
INNER JOIN boughtProduct AS bp ON bp.boughtProductID = bpt.boughtProductID
WHERE r.repairID = ?

I did modify this to a SELECT Query changing DELETE bp, t, r to SELECT * and it did return all the records. So my question is what’s wrong with this query?

Database Tables

Solution #1:

I believe in those cases, best practice is defining foreign key constraints on the tables with ON DELETE CASCADE option.

This way deleting the record from the parent table removes the records from child table, and you only need to deal with one as long as there’s a relation.


If you are still looking to perform multiple deletes using a query, it seems to be possible, but not using a new-syntax join. From the manual:

You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the particular condition in the WHERE clause. However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 12.2.8.1, “JOIN Syntax”.

The manual includes an example as well:

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

I expect something along the lines of the following will work for you (I haven’t the DB so I can’t actually test the query):

DELETE bp, t, r
FROM repair AS r, taskRepair AS tr
INNER JOIN task AS t ON t.taskID = tr.taskID
LEFT JOIN boughtProductTask AS bpt ON bpt.taskID = t.taskID
INNER JOIN boughtProduct AS bp ON bp.boughtProductID = bpt.boughtProductID
WHERE r.repairID = tr.repairID AND r.repairID = ?

Edit – second suggestion:

DELETE bp, t, r
FROM repair AS r,
     task AS t,
     taskRepair AS tr,
     boughtProduct AS bp
LEFT JOIN boughtProductTask AS bpt ON bpt.taskID = t.taskID
WHERE r.repairID = tr.repairID
      AND t.taskID = tr.taskID
      AND bp.boughtProductID = bpt.boughtProductID
      AND r.repairID = ?

Note how I have changed the JOIN with taskRepair to another table in the FROM list, and added the condition all the way down inside the WHERE clause.


Sources:

Respondent: Selfish
The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Leave a Reply

Your email address will not be published.