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 2 of 2
  1. #1
    New Coder
    Join Date
    Feb 2012
    Posts
    36
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Question DELETE statement help

    I have a guest id that exists in 3 different tables. I want to delete a guest in one step. How do I use the DELETE statement to delete all data associated with that guest id in all 3 tables with out having to do it 3 separate times ?

    Here's what I have:

    Code:
    DELETE FROM guests WHERE guest_id = $guest_id
    DELETE FROM guest_dates WHERE guest_id = $guest_id
    DELETE FROM links WHERE guest_id = $guest_id
    I also tried:

    Code:
    DELETE FROM guests, guest_dates, links WHERE guest_id = $guest_id
    Then I learned you can only delete from one table with the delete statement.

    What other way can I accomplish this ?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,677
    Thanks
    80
    Thanked 4,644 Times in 4,606 Posts
    The only way you could do that in one step would be:
    (a) Be sure you are using INNODB tables, not MyISAM.
    (b) Be sure you have specified correct PRIMARY KEY on the guests table.
    (c) Be sure you have specified correct FOREIGN KEY contrasints on the other two tables.
    (d) Be sure that, in the FOREIGN KEY contraints, you specify ON DELETE CASCADE.

    That is:
    Code:
    CREATE TABLE guests ( 
        guest_id INT PRIMARY KEY, ...
    ) ENGINE INNODB;
    
    CREATE TABLE guest_dates (
       guest_id INT, 
       CONSTRAINT FOREIGN KEY guest_id REFERENCES guests(guest_id) ON DELETE CASCADE, 
       ...
    ) ENGINE INNODB;
    
    CREATE TABLE links (
       guest_id INT, 
       CONSTRAINT FOREIGN KEY guest_id REFERENCES guests(guest_id) ON DELETE CASCADE, 
       ...
    ) ENGINE INNODB;
    *NOW* if you simply do
    Code:
    DELETE FROM guests WHERE guest_id = 999;
    the corresponding records will be deleted from guest_dates and links automatically.

    Otherwise, you must delete from each table individually.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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