Mysql remove duplicate data or rows with DISTINCT

delete from table1
USING table1, table1 as vtable
WHERE (NOT table1.ID=vtable.ID)
AND (table1.field_name=vtable.field_name)

1. Here you tell mysql that there is a table1.
2. Then you tell it that you will use table1 and a virtual table with the values of table1.
3. This will let mysql not compare a record with itself!
4. Here you tell it that there shouldn’t be records with the same field_name.

I have fixed this problem and have a tutorial on my website: http://www.weareallhumansnow.com

The first answer by jafar is close, you just need some condition that is true for all except one of the duplicates.
I ended up using:

delete from table1
USING table1, table1 as vtable
WHERE (table1.ID > vtable.ID)
AND (table1.field_name=vtable.field_name)

Used “greater than” instead of “not equal”. This handles both the conditions that the records not be the same and that only one record will return false.
Since we use greater than (or less than), only the first (or last) duplicate returns false, so it is not selected for deletion.

this could also help

before :

id | name
————-
1 | test |
2 | test |
3 | php |
4 | echo |

after:

id | name|
————
2 | test |
3 | php |
4 | echo |

<?php
$cn= mysql_connect("localhost", "root", "") or die('Could not connect');
$db= mysql_select_db(test, $cn) or die('Could not select database');

    $query="SELECT * FROM name"; 

    $result=mysql_query($query);
    echo mysql_error();

        while($row = mysql_fetch_array($result, MYSQL_BOTH))

        {
                $query1="SELECT * FROM tablename where name = '".$row[1]."'"; 

                $result1=mysql_query($query1);
                $count = mysql_num_rows($result1) - 1;

                mysql_query("DELETE FROM tablename WHERE name='".$row[1]."' LIMIT $count",$link);
                echo "deleted $row[1] ";

}
        echo mysql_error();
    ?>

This is the proper format, allowing for easy debugging of errors…
DELETE
FROM table1
USING table1, table1 AS vtable
WHERE vtable.id > table1.id
AND table1.field_name = vtable.field_name

If you’re nervous about deleteing everything, you can make sure it’s working first:
SELECT *
FROM table1, table1 AS vtable
WHERE vtable.id > table1.id
AND vtable.field_name = table1.field_name

I just did this and it worked like a charm on hundreds of thousands of database entries.

- Caleb

The “id” is the primary key column.

SpyGM is spot on – and saved me a massive amount of time. His solution allows you to delete duplicates based on a combination of columns, rather than just a simple 1 to 1 comparison.

The code I used for my table as follows (based on SpyGM’s comment):

ALTER IGNORE TABLE `referrer` ADD UNIQUE INDEX(user_id,referrer_id);

But as I understand it you could have more than 2 columns in here, or just the 1 if that’s all you need.

Cheers

SpyGM’s solution works for me.

I’d guess it was Mike Chirico’s original he found: http://free.netartmedia.net/Databases/Databases8.html ;o)

This works:

– remove duplicated entries
– —————————————————————————————
– step 1
CREATE TABLE temp_table AS
SELECT * FROM foo WHERE 1 GROUP BY bar;
– step 2
DROP TABLE foo;
– step 3
RENAME TABLE temp_table TO foo;
– —————————————————————————————

Ruggie is wrong. I tested the ALTER IGNORE command, and it will not remove row with id 3 in his example – just id=4 will be deleted.

I could not even read the article because a banner ad was in the way that I could not close. This is a lame web site.