SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard
    Join Date
    Nov 2003
    Location
    United Kingdom
    Posts
    2,118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    delete id's from one table that dont' match the id's of another mysql table using php

    Hi,

    I have two mysql tables. I have table A that has all the same id's as table B. Table B also has the same id's as table A but it also contains quite a few more id's that is not needed and is not in table A.

    So this means that Table A has around 300 rows and Table B has about 380 rows.

    How can I do it using php or just one mysql query so that I can delete the rows in Table B that don't match the id's of Table A. So in other words, how can I delete the extra 80 rows in Table B that doesn't have the same id's as Table A.

    Thanks.

  2. #2
    SitePoint Member
    Join Date
    Apr 2004
    Location
    Orange County
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    get all of the id's from table a
    then do delete from table b where columnid not in(all of the id's from table a go here)

  3. #3
    SitePoint Wizard
    Join Date
    Nov 2003
    Location
    United Kingdom
    Posts
    2,118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks you are a great help. I wish I had your knowledge of programming.

    I knew there was a way, but I just became clueless.

  4. #4
    SitePoint Addict
    Join Date
    Jan 2005
    Location
    United Kingdom
    Posts
    208
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's possible in one query...
    Code:
    DELETE FROM table1 USING table1 LEFT JOIN table2 ON table1.col = table2.col WHERE table2.col IS NULL


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
  •