SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Deleting From Multiple Tables

    Hi all,

    I want to delete data from two tables. How do I do this? Below query doesn't work. Problem with syntax I think.
    Get this error:
    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 'AS s INNER JOIN details AS d ON s.ffnumber = d.ffnumber WHERE s

    PHP Code:
     $query "DELETE FROM student AS s "
                       
    "INNER JOIN ".
                       
    "details AS d ".
                      
    "ON s.ffnumber = d.ffnumber ".
                       
    "WHERE s.ffnumber = '$value' ".
                      
    "AND ".
                      
    "d.ffnumber = '$value' "

  2. #2
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    why not just run two queries if they are that small?
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2005
    Location
    Venlo, the Netherlands
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What you're trying to do is not possible as far as I'm aware, you can't delete from two tables in one command
    Have a look at foreign key constraints (though I'm not sure if they are fully supported under mysql):

    FOREIGN KEY ffnumber
    REFERENCES details
    ON DELETE CASCADE

    On the other hand I'm wondering if your student and details tables don't hold a 1 on 1 relationship. In that case just
    put the one together under one table.

  4. #4
    SitePoint Zealot
    Join Date
    Aug 2005
    Location
    Bucharest, Romania
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  5. #5
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't think for MySQL, deleting from multiple tables allows INNER JOIN. Check the syntax and examples from the link above.
    - Son Nguyen
    AdSpeed.com - Ad Serving and Ad Management Made Easy

  6. #6
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you can delete from multiple tables at once, but you need to be using mysql 4.1 in order to do it. Older versions of mysql do not support multiple table deletes. The link above shows the syntax,


    DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*] ...]
    USING table_references
    [WHERE where_definition]

    so the query you want should be something like

    $query = "DELETE FROM student AS s, details AS d ".
    "INNER JOIN details ".
    "ON s.ffnumber = d.ffnumber ".
    "WHERE s.ffnumber = '$value' ".
    "AND ".
    "d.ffnumber = '$value' ";

    actually, look at your above query.. it makes no sense.. if you are doing a join there is no need to specify that the two columns both have to be = to $value, thats who whole point of writing inner join... you could get rid of the inner join or the part after AND

  7. #7
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Multiple table deletion is supported in 4.0.x

    From http://dev.mysql.com/doc/mysql/en/delete.html
    The first multiple-table DELETE syntax is supported starting from MySQL 4.0.0. The second is supported starting from MySQL 4.0.2. The table_references part lists the tables involved in the join. Its syntax is described in Section 13.2.7.1, “JOIN Syntax”.
    - Son Nguyen
    AdSpeed.com - Ad Serving and Ad Management Made Easy

  8. #8
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you are right, it just needs to be 4.0 or greater,.
    I had a problem where i couldnt get multiple deletes working because i was using 3.23 and I thought someone had specified that you needed 4.1

  9. #9
    SitePoint Addict
    Join Date
    Jul 2005
    Posts
    215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    delete table

    .


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
  •