Jump to content

Delete query that deletes from 3 tables


pocobueno1388

Recommended Posts

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.

Link to comment
Share on other sites

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 :)

Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.