MySQL Delete from multiple tables in one query

Question

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?

I am guessing that the last inner join should be left join, because it turns earlier outer joins into inner joins. — Gordon Linoff
– Gordon Linoff, Commented Sep 28, 2015 at 1:10

Selfish · Accepted Answer · 2015-09-28 01:57:10Z

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:

edited Sep 28, 2015 at 1:57

answered Sep 28, 2015 at 1:27

Selfish

6,2304 gold badges47 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Joseph118 Over a year ago

As for your first answer, the primary keys on my linking tables are also acting as a foreign key which has the ON DELETE CASCADE constraint on them. I was hoping that all I needed to do was delete the parent... I did test your query and it returned the same results as my query.. I'll try some modification with your query however, you have given me some ideas

Selfish Over a year ago

Hey @Joseph118, I have added a second suggestion in the answer. let me know.

Joseph118 Over a year ago

error... 'Unknown column 't.taskID' in 'on clause' '.. it's not making any sense to me

Selfish Over a year ago

@Joseph118, try moving the ON condition to the WHERE clause. Totally removing the ON.

Selfish Over a year ago

@Joseph118, replace the DELETE with SELECT, and try to figure why results are missing from the set. I'm starting to believe this has nothing to do with DELETE syntax.

|

Collectives™ on Stack Overflow

MySQL Delete from multiple tables in one query

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related