[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
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?
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 126.96.36.199, “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
taskRepair to another table in the
FROM list, and added the condition all the way down inside the