Deleting rows from a MySQL database based on multiple LEFT JOIN with foreign key constraints

Something that I hadn’t attempted to do before, probably because I was trying to delete something from a medium-sized JOIN query that I hadn’t written using a WHERE clause, is realise that unlike deleting with INNER JOIN you cannot delete using a LEFT JOIN where the clause may or may not exist in various target tables in a SELECT query.

(put an example of original query here)

Thus if WHERE y IS NULL is actually null because the join doesn’t match any records in the other table(s) you can’t delete on that clause with a single query (which is better explained on this blog)

The way I figured to do it is create a master list of keys used for the JOIN operation (the keys you’re using for ON or USING) and export it as a comma-delimited list; you can use a sub-query if you’re not deleting from the table where the keys exist as primary. Example: if you’re deleting a list of products which have never been ordered using a cheque, you can’t get the product keys using a sub-query if you’re deleting the products themselves in the same batch query.

(put an example of key query here)

Then, get the list and DELETE FROM each target table using IN and the list of keys you previously generated. This way each table will be deleting from a list of keys that may or may not have matched in the original SELECT query; whether they did match or not doesn’t matter since it’s using IN, it’ll work without a sub-query so you can run it on all tables, and MySQL won’t complain since you’re targeting each table individually one at a time.

(put an example of delete batch query here)

You may need to disable foreign key checking to do this as the queries would be run in succession, not as a single query, so the dependencies would temporarily break.

Add comment