SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Delete non distinct

    Hi,

    Kinda tricky question I think, maybe not. But here it goes:

    1. How'd I extract a list of topics from the database that has the same name?

    2. How would a clear this database so that the duplicates do not exist?

    Eg:

    ID Name
    1 Silly Me
    2 Silly Me
    3 Silly You
    4 Silly You
    5 Silly You
    6 Silly Everyone

    After the script has run it should leave

    ID Name
    1 Silly Me
    3 Silly You
    6 SIlly Everyone

    Thanks if anyone has the answer
    -Peter

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, sorry that this is a vague and dubios answer - but I'm just about to fall into bed

    I think you can do something like so - but I'm feeling pretty delerious...

    DELETE FROM TableName
    GROUP BY Name
    HAVING COUNT(*) > 1

  3. #3
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    This is what I'm using:

    DELETE FROM emails GROUP BY email WHERE listID='1' HAVING COUNT(*) > 1

    Now this gives the error:

    MySQL said: You have an error in your SQL syntax near 'GROUP BY email WHERE listID='1' HAVING COUNT(*) > 1' at line 2

    Any ideas?

  4. #4
    You want what? By when?? Milamber's Avatar
    Join Date
    Jan 2001
    Location
    California
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    for future reference, to keep the fields from doing that, simply make the column unique when you create it.
    -Jeff Minard | jrm.cc - Battlefield 2 Stats

  5. #5
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Pete,

    Before you go deleting duplicate entries, is there any data in the database that refers to the entries you would be deleting...? If so, that will cause problems.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  6. #6
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope there isn't.

  7. #7
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Ok, my suggestion, then, would be to do a SELECT DISTINCT into a temporary table, then copy the new table over top of the old table using a RENAME TABLE query.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  8. #8
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, thanks.
    -Peter


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
  •