Delete query that deletes from 3 tables

[pocobueno1388]

I'm stumped on how to write a delete query that will delete rows from 3 different tables. I'm programming a forum and users can add/delete a category. If a category is deleted, I want to delete all threads/posts from that category.

 

Here are the table structures:

-----------------------
TABLE forum_categories
-----------------------
categoryID +---------------------+
name                             |
description                      |
                                 |
-----------------------          |
TABLE forum_threads              |
-----------------------          |
threadID ---------------------------+
categoryID +---------------------+  |
userID                              |
subject                             |
body                                |
date                                |
                                    |
-----------------------             |
TABLE forum_replies                 |
-----------------------             |
replyID                             |
threadID +--------------------------+
userID
body
date

 

So here are the queries I would like to combine

DELETE FROM forum_categories WHERE categoryID=$categoryID

DELETE FROM forum_threads WHERE forum_threads.categoryID=forum_categories.categoryID

DELETE FROM forum_replies WHERE forum_replies.threadID=forum_threads.threadID

 

And here is my failed attempt at the query:

DELETE FROM forum_categories fc, forum_threads ft, forum_replies fr
USING fc, ft, fr 
WHERE fc.categoryID=ft.categoryID
AND ft.threadID=fr.threadID
AND fc.cateogryID=$categoryID

 

I'm not sure that I understand the syntax, and what exactly the USING part is.

 

Any help with this from the MySQL gurus would be greatly appreciated Thanks.

[fenway]

Easiest way is to convert this into a select with proper join syntax first, then convert to either of the valid delete syntaxes.

[pocobueno1388]

Okay, here is my attempt at a select query that would select all threads within a category, and all replies to those threads.

 

SELECT fc.name as category_name, ft.threadID, ft.subject as thread_subject, fr.replyID, fr.body as reply_body, fr.threadID as reply_to_threadID
FROM forum_categories fc
JOIN forum_threads ft
ON ft.categoryID = fc.categoryID
JOIN forum_replies fr
ON fr.threadID = ft.threadID
WHERE fc.categoryID = 1

 

It was close, but it only selects the threads and replies to the threads that have replies. I would also like to select threads that don't have any replies on them. Am I using the wrong join? I tried others, but got the same results.

 

Thanks for the help

[pocobueno1388]

Bump

[keeB]

Take a look at LEFT JOIN. This will give you what you want

 

FYI -- You're doing an INNER JOIN.

[pocobueno1388]

Ah, thanks. Thats seems to cover everything now. I could have sworn I tried a LEFT JOIN, maybe I was thinking that JOIN was the same as LEFT JOIN, but it's INNER JOIN.

 

Now how would I turn this into a delete query? I'm baffled by the syntax in the manual.

 

DELETE
FROM forum_categories fc
LEFT JOIN forum_threads ft
ON ft.categoryID = fc.categoryID
LEFT JOIN forum_replies fr
ON fr.threadID = ft.threadID
WHERE fc.categoryID = 1

 

 

[fenway]

You you need list who tables you want to delete.

[pocobueno1388]

You you need list who tables you want to delete.

I'm sorry, I don't follow...

 

I attempted to follow the manual, and this is what I came up with:

DELETE 
   forum_categories, forum_threads, forum_replies
FROM 
   forum_categories fc, forum_threads ft, forum_replies fr
WHERE
   ft.categoryID = fc.categoryID AND
   fr.threadID = ft.threadID AND
   fc.categoryID = 1

 

I get the error (the table "forum_categories" DOES exist):

#1109 - Unknown table 'forum_categories' in MULTI DELETE

 

Am I not supposed to put the table names I want to delete rows from after the DELETE part?

[BlueSkyIS]

no. if you want to delete all records, leave out anything between DELETE and FROM

[pocobueno1388]

Okay, I just ran this query:

DELETE
FROM 
   forum_categories fc, forum_threads ft, forum_replies fr
WHERE
   ft.categoryID = fc.categoryID AND
   fr.threadID = ft.threadID AND
   fc.categoryID = 1

 

Error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE
   ft.categoryID = fc.categoryID AND
   fr.threadID = ft.threadID AND
' at line 4 

[fenway]

You you need list who tables you want to delete.

I'm sorry, I don't follow...

 

I attempted to follow the manual, and this is what I came up with:

DELETE 
   forum_categories, forum_threads, forum_replies
FROM 
   forum_categories fc, forum_threads ft, forum_replies fr
WHERE
   ft.categoryID = fc.categoryID AND
   fr.threadID = ft.threadID AND
   fc.categoryID = 1

 

I get the error (the table "forum_categories" DOES exist):

#1109 - Unknown table 'forum_categories' in MULTI DELETE

 

Am I not supposed to put the table names I want to delete rows from after the DELETE part?

If you use the aliases, I'm assuming you need to refer to them as aliases.

[pocobueno1388]

Okay, it's getting very close to working now.

 

The problem now is it only deletes the threads and replies that have at least one reply. So if there are no replies on a thread it leaves that thread alone, and I want it deleted.

 

Here is the query:

DELETE 
   forum_threads ft, forum_replies fr
FROM 
   forum_categories fc, forum_threads ft, forum_replies fr
WHERE
   ft.categoryID = fc.categoryID AND
   fr.threadID = ft.threadID AND
   fc.categoryID = 3

 

I'm assuming I have to add something else to the WHERE clause, but I have already linked each table together...am I missing something?

[fenway]

That's back to a LEFT JOIN.

[pocobueno1388]

That's back to a LEFT JOIN.

 

Is that because I took one of the tables out of the DELETE clause? I did that because I didn't want it to actually delete the category, just the threads and replies that were in that category.

 

Is there anyway to get it to work without that third table listed?

[teng84]

DELETE

  forum_threads ft, forum_replies fr

FROM

  forum_categories fc, forum_threads ft, forum_replies fr

WHERE

  ft.categoryID = fc.categoryID AND

  ft.threadID = 3 OR

  fc.categoryID = 3

 

maybe?

[pocobueno1388]

DELETE

  forum_threads ft, forum_replies fr

FROM

  forum_categories fc, forum_threads ft, forum_replies fr

WHERE

  ft.categoryID = fc.categoryID AND

  ft.threadID = 3 OR

  fc.categoryID = 3

 

maybe?

 

No, thats not it. I don't want the threadID OR the categoryID to be 3, I only want the categoryID to be 3.

[fenway]

That's back to a LEFT JOIN.

 

Is that because I took one of the tables out of the DELETE clause? I did that because I didn't want it to actually delete the category, just the threads and replies that were in that category.

 

Is there anyway to get it to work without that third table listed?

The query you use to find the rows in each of the tables has nothing to do with which ones are deleted -- so you can still join however you choose, and only select the ones you want.

[pocobueno1388]

All I did was change this:

 

DELETE 
   forum_categories, forum_threads, forum_replies

 

To:

DELETE 
   forum_threads ft, forum_replies fr

 

That stopped it from deleting the row in "forum_categories". So are you sure the tables listed there have nothing to do with what is deleted?

 

My current query is still:

DELETE 
   forum_threads ft, forum_replies fr
FROM 
   forum_categories fc, forum_threads ft, forum_replies fr
WHERE
   ft.categoryID = fc.categoryID AND
   fr.threadID = ft.threadID AND
   fc.categoryID = 3

 

I'm completely lost on what to do next.

[zenag]

single query to delete from 3 tables

 

DELETE FROM forum_categories, forum_threads ,forum_replies

  USING forum_categories LEFT JOIN forum_threads  ON forum_categories.categoryID = forum_threads.categoryID left join forum_replies on forum_replies.threadID=forum_threads.categoryID

  WHERE forum_categories.categoryID = 1

 

[pocobueno1388]

EDIT: Hold on guys, I've been testing it and I think I see a problem.

 

zenag - Thank you, that worked great. All I had to do was take "forum_categories" out of the DELETE FROM clause.

 

Here is the final query that worked:

DELETE FROM 
   forum_threads ,forum_replies
USING 
   forum_categories 
LEFT JOIN forum_threads 
   ON forum_categories.categoryID = forum_threads.categoryID 
LEFT JOIN forum_replies 
   ON forum_replies.threadID=forum_threads.categoryID
WHERE 
   forum_categories.categoryID = 1

 

Thanks fenway, keeB, zenag, and BlueSkyIS for all the help! I appreciate it.

[pocobueno1388]

Okay, I fixed the glitch.

 

Final query:

DELETE FROM 
   forum_threads ,forum_replies
USING 
   forum_categories 
LEFT JOIN forum_threads 
   ON forum_categories.categoryID = forum_threads.categoryID 
LEFT JOIN forum_replies 
   ON forum_replies.threadID=forum_threads.threadID
WHERE 
   forum_categories.categoryID = 1