SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Delete Excess Rows

    Say I have table1 t1, and table t2, and they're related by t1.id=t2.rid. t1 has had rows deleted, so there are now excess rows in t2 that are related to non-existent rows in t1. how can i delete these excess rows in t2?
    michael.Crabbe

  2. #2
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Kansas City, MO
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    Delete from t2 where t2.rid not in (select t1.id from t1);

  3. #3
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Say t2 and t1 both have about 9,000 rows.

    This takes about 10 minutes to execute through phpMyAdmin. Should it take that long or should the tables perhaps have a more optimised structure?

    Or is there a quicker way of doing this altogether?
    michael.Crabbe

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    9000 rows is completely insignificant in size. you should though, have those columns indexed.

    your performance should be talking about seconds at the most.

  5. #5
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Added an index and took less than a second for several of those queries

    Thanks.
    michael.Crabbe


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
  •