SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Member
    Join Date
    May 2004
    Location
    NC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Delete related rows from 3+ tables

    Hi:
    I'm trying to write my first join. I want to delete related rows from multiple tables. My query isn't creating an error, it's just not showing any results. Deleted rows always equals "0". Here's my query (names substituted):

    DELETE table_1, table_2, table_3, table_4
    FROM table_1, table_2, table_3, table_4
    WHERE $ID_num = table_1.ID_num
    AND $ID_num = table_2.ID_num
    AND $ID_num = table_3.ID_num
    AND $ID_num = table_4.ID_num

    If it's not clear, what I'm trying to do is delete all rows from the tables where ID_num = $ID_num. $ID_num is a php variable, but I can't get it to work if I hardcode the value (for example: 25) either. Can I have a variable in the query like that or does it have to be some value relative to the mysql table? I'm a little confused about how joins work. thanks in advance-

  2. #2
    Non-Member
    Join Date
    Aug 2002
    Location
    USA
    Posts
    544
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can't delete from more than one table.

  3. #3
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    with MySQL you can indeed delete rows from more than one table:

    http://dev.mysql.com/doc/mysql/en/DELETE.html

    So your example should do something. Which version are you using? According to the manual it has to be at least 4.0.
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can't delete a table like your query is trying to do, you have to drop a table.

    If you want to delete rows change the top row to just DELETE.

  5. #5
    SitePoint Member
    Join Date
    May 2004
    Location
    NC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for responding.

    I'm using 4.0.18, so I thought it should work. I also tried changing the top row to just DELETE but that created a syntax error.

    any other ideas?

  6. #6
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    @daveman
    chillnc's example doesn't delete tables. It's just a MySQL-specific way to delete rows from more than one table. The first list of tables after DELETE is mandatory. Here you have to specify the tables from which you want to delete rows from. These can be less than those tables which you specify after the FROM-clause.

    @chillnc

    I've just checked it again and I don't see any problem with your example:

    mysql> delete bezstaffeln, bezebenen from bezstaffeln, bezebenen where 'LLS' = bezstaffeln.fldBezeichnung and 'LLS' = bezebenen.fldBezeichnung;
    Query OK, 2 rows affected (0.00 sec)
    Just another thought: did you check the value of $ID_num respectively the value of the entire query string?
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  7. #7
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the clarification on that kleineme.

  8. #8
    SitePoint Member
    Join Date
    May 2004
    Location
    NC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I figured out the problem. The query works as long as there are matching rows in every table that meet the WHERE condition.

    However, if there are rows in 3 of the tables that meet the condition, but no rows in the 4th one, then NONE of the rows will get deleted. Any ideas as to how to get around this? thanks

  9. #9
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Replace AND with OR ???

    (I've only go an older version so I can't test it.)

  10. #10
    SitePoint Member
    Join Date
    May 2004
    Location
    NC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, that wasn't a good thing to do, LOL. Replacing AND with OR deleted every row in every table. Any other ideas?

  11. #11
    SitePoint Member
    Join Date
    May 2004
    Location
    NC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This deletes the correct rows from the child tables (even if some of the tables don't have matching rows), but it also deletes everything from the parent table:

    DELETE table_1, table_2, table_3, table_4
    FROM table_1
    LEFT JOIN table_2 ON $ID_num = table_2.ID_num
    LEFT JOIN table_3 ON $ID_num = table_3.ID_num
    LEFT JOIN table_4 ON $ID_num = table_4.ID_num

    I think a left join is a move in the right direction, but I can't quite figure it out. thanks

  12. #12
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    what about adding a WHERE $ID_num = table_1.ID_num to your query?
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  13. #13
    SitePoint Member
    Join Date
    May 2004
    Location
    NC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    nope this generates a syntax error:

    DELETE table_1, table_2, table_3, table_4
    FROM table_1 WHERE $ID_num = table_1.ID_num
    LEFT JOIN table_2 ON $ID_num = table_2.ID_num
    LEFT JOIN table_3 ON $ID_num = table_3.ID_num
    LEFT JOIN table_4 ON $ID_num = table_4.ID_num

  14. #14
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    what I meant to say was this:

    Code:
    DELETE table_1, table_2, table_3, table_4
    FROM table_1 
    LEFT JOIN table_2 ON $ID_num = table_2.ID_num
    LEFT JOIN table_3 ON $ID_num = table_3.ID_num
    LEFT JOIN table_4 ON $ID_num = table_4.ID_num
    WHERE $ID_num = table_1.ID_num;
    but it's untested and actually I do not really think that it will get you the desired effect
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  15. #15
    SitePoint Member
    Join Date
    May 2004
    Location
    NC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Kleineme!

    Du bist toll! That was it: The WHERE clause goes after the joins. It works exactly the way I need it too. danke!

  16. #16
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    keine Ursache!

    I'm a bit surprised though why it works. Obviously true understanding of multi-table-deletion is over my head. That's why I'd prefer to do it with four queries instead of one. The other reason is that you can't do it with other DBs
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.


Bookmarks

Posting Permissions

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