Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Mar 2006
    Location
    In an apartment.
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple table delete

    Hello-

    Here is my scenario: I want to be able to delete rows from three different tables, A, B, and C, using the the same two keys (1 and 2) for each one. The catch is that data might not exist in Table B, and even if it does, it might not exist in Table C (the query wouldn't exist if there was no matching row in Table A).

    After much wailing and gnashing of teeth I came up with a query in this format:

    Code:
    DELETE A, B, C  FROM A aa, B bb, C cc  
            WHERE aa.1 = '1' 
    	AND bb.1 = '1'
    	AND cc.1 = '1'
    	AND aa.2 = '2'
    	AND bb.2 = '2'
        AND cc.2 = '2'
    The works great if matching data exists in all three tables, but not at all when matching data doesn't exist in B and/or C.

    So is there a different syntax I can use or should I stick with three queries?

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    any reason why you don't just run 3 deletes? which would be easier and probably faster...

    any reason why you don't use an ON DELETE cascade on two of these tables?
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    New Coder
    Join Date
    Mar 2006
    Location
    In an apartment.
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well, I don't know these things so that's why I ask questions.

    To me it would seem counter-intuitive that three queries would be faster but I'm not all that familiar with MySQL.

    As for CASCADE, please correct me if I'm wrong, but that would be a setting in the database itself, not part of a query that would execute due to certain actions requested via a webpage. The database I'm using is based on what must now be an outdated structure and doesn't have constraints built into it but that would be very useful. Something to consider for the future.

    Thanks for the tips- I'll stick with three queries for now.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •