MySQL delete multiple rows if they exist

Question

first of all, thank you for sharing your experience and wasting time on me. I searched within many threads, but still can't find the answer i'm looking for. Well, let's move to the question. How can i write a MySQL statement, that looks into different tables for results, if it finds a fit, it will be deleted. It's really hard to explain, so i think a better way would be to show an example.

DELETE FROM forum_threads, forum_comments, forum_categories 
USING forum_categories 
INNER JOIN forum_threads 
INNER JOIN forum_comments 
WHERE forum_categories.name = ? 
  AND forum_threads.category_id = forum_categories.id 
  AND forum_comments.thread_id = forum_categories.id

This code above works fine, but only, if three tables has an row, other case it returns that zero rows were affected.

So, i want to write a statement, which deletes rows from categories, threads and comments table, if exists even one of them. Example 1: forum_categories has an row, but forum_threads and forum_comments does not have any, so delete only from categories. Example 2: forum_categories has an row and forum_threads has multiple rows, but forum_comments does not have any, so delete from categories and from threads. Example 3: forum_categories does not have any row, but forum_threads has multiple rows and forum_comments has multiple rows, so don't do anything.

Each case forum_categories has only one row, and forum_threads and forum_comments can have from multiple to zero.

I tried using LEFT JOIN did not work or i am doing it wrong.

Answer 1

My guess is that simply changing to a LEFT JOIN won't work because the WHERE clause creates an implicit INNER JOIN. You've got to use ON. I'm not sure why MySQL even allows you to write JOIN syntax like that.

Try:

DELETE forum_threads, forum_comments, forum_categories 
FROM forum_categories 
LEFT JOIN forum_threads 
    ON forum_threads.category_id = forum_categories.id 
LEFT JOIN forum_comments 
    ON forum_comments.thread_id = forum_categories.id
WHERE forum_categories.name = ?;

Or:

DELETE FROM forum_threads, forum_comments, forum_categories 
USING forum_categories 
LEFT JOIN forum_threads 
    ON forum_threads.category_id = forum_categories.id 
LEFT JOIN forum_comments 
    ON forum_comments.thread_id = forum_categories.id
WHERE forum_categories.name = ?;

Answer 2

This predicate doesn't look right:

forum_comments.thread_id = forum_categories.id

If the _comment table has a foreign key referencing _category(id), we'd expect that column would be named category_id.

Given a column named thread_id in the _comment table, that looks way more like a foreign key reference to _thread(id).

But, we don't have any information about the schema, other than what we can surmise from the statement.

To use "outer joins" in a DELETE statement, you can do it like this:

DELETE co.*
     , th.*
     , ca.*
  FROM forum_category ca
  LEFT 
  JOIN forum_thread th
    ON th.category_id = ca.id 
  LEFT
  JOIN forum_comment co
    ON co.thread_id = th.id
WHERE ca.name = ?

Note that if there are foreign key references, MySQL may attempt to delete rows from the tables in an order which causes a foreign key violation/exception to be thrown.

That is, we'd like MySQL to delete rows first from the _comment table, then from the _thread table, and then from the _category table. But MySQL doesn't give us that guarantee.

A couple of ways to address that:

1) declare the foreign keys with "ON DELETE CASCADE";

2) disable the foreign key checks (set FOREIGN_KEY_CHECKS=0), run the DELETE, and then re-enable the foreign keys (set FOREIGN_KEY_CHECKS=1);

3) run three separate statements; the statements can be identical, except for what comes after DELETE, e.g.:

DELETE co.* FROM ... ;
DELETE th.* FROM ... ;
DELETE ca.* FROM ... ;

(But this is subject to a race condition, where another session could insert a row to _thread, after we've deleted from _thread, before we get to the delete from _category.