1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

SQL Query to Delete Duplicates

Discussion in 'Databases' started by Fahd, Jan 9, 2006.

Thread Status:
Not open for further replies.
  1. #1
    Hello,

    I have a table example:
    SEMrush
    ID | Name | Email

    I want to delete duplicate records/rows where email is the duplicate.

    1 | F |
    2 | D |
    3 | P |

    I need an sql query that will delete the duplicated records and maintain only one. It doesn't matter which ones get deleted.

    Thanks in advance for any help!
     
    Fahd, Jan 9, 2006 IP
    SEMrush
  2. stumpyPete

    stumpyPete Peon

    Messages:
    49
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #2
    stumpyPete, Jan 9, 2006 IP
  3. toughguy

    toughguy Well-Known Member

    Messages:
    846
    Likes Received:
    26
    Best Answers:
    0
    Trophy Points:
    118
    #3
    toughguy, Jan 12, 2006 IP
  4. Shoemoney

    Shoemoney $

    Messages:
    4,475
    Likes Received:
    588
    Best Answers:
    0
    Trophy Points:
    295
    #4
    I just did this the other day here is how i did it

    I had a bunch of zipcodes i wanted to remove the doups. I found this the easiest way to do it in PHP

    mysql_connect("$db_host","$db_user","$db_passwd");
    mysql_select_db("$db_db");

    $results = mysql_query("SELECT zipcodes, COUNT(*) as count FROM zipcodes GROUP BY zipcodes order by count desc");

    while($row = mysql_fetch_object($results))
    {

    $zipcodes= $row->zipcodes;
    if ($count >1) {
    print "$zipcodes<br> ";
    mysql_query("delete from zipcodes where id='$id'");


    This will loop through each time you run it and delete 1 douplicate where you have more then 1 .

    course dont use exactly what i posted it its just off the top of my head from what i remember
     
    Shoemoney, Jan 12, 2006 IP
  5. l234244

    l234244 Peon

    Messages:
    1,225
    Likes Received:
    50
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Not sure if this helps, but it might be worth going back to the original script and placing a check system so users with the same name and email address can not register.
     
    l234244, Jan 12, 2006 IP
  6. Shoemoney

    Shoemoney $

    Messages:
    4,475
    Likes Received:
    588
    Best Answers:
    0
    Trophy Points:
    295
    #6
    well just add a uniq key to the row in the db... that will solve that
     
    Shoemoney, Jan 12, 2006 IP
Thread Status:
Not open for further replies.