pocobueno1388 Posted March 9, 2008 Share Posted March 9, 2008 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 10, 2008 Share Posted March 10, 2008 Easiest way is to convert this into a select with proper join syntax first, then convert to either of the valid delete syntaxes. Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted March 16, 2008 Author Share Posted March 16, 2008 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 Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted March 18, 2008 Author Share Posted March 18, 2008 Bump Quote Link to comment Share on other sites More sharing options...
keeB Posted March 18, 2008 Share Posted March 18, 2008 Take a look at LEFT JOIN. This will give you what you want FYI -- You're doing an INNER JOIN. Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted March 19, 2008 Author Share Posted March 19, 2008 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 Quote Link to comment Share on other sites More sharing options...
fenway Posted March 19, 2008 Share Posted March 19, 2008 You you need list who tables you want to delete. Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted March 19, 2008 Author Share Posted March 19, 2008 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? Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted March 19, 2008 Share Posted March 19, 2008 no. if you want to delete all records, leave out anything between DELETE and FROM Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted March 19, 2008 Author Share Posted March 19, 2008 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 Quote Link to comment Share on other sites More sharing options...
fenway Posted March 19, 2008 Share Posted March 19, 2008 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. Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted March 19, 2008 Author Share Posted March 19, 2008 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? Quote Link to comment Share on other sites More sharing options...
fenway Posted March 19, 2008 Share Posted March 19, 2008 That's back to a LEFT JOIN. Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted March 20, 2008 Author Share Posted March 20, 2008 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? Quote Link to comment Share on other sites More sharing options...
teng84 Posted March 20, 2008 Share Posted March 20, 2008 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? Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted March 20, 2008 Author Share Posted March 20, 2008 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 20, 2008 Share Posted March 20, 2008 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. Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted March 25, 2008 Author Share Posted March 25, 2008 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. Quote Link to comment Share on other sites More sharing options...
zenag Posted March 25, 2008 Share Posted March 25, 2008 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 Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted March 25, 2008 Author Share Posted March 25, 2008 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. Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted March 25, 2008 Author Share Posted March 25, 2008 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.