duplicates in mySQL - how can I remove?

[Wm]

I have a table of users in mySQL that appears to have a lot of duplicates.
What's the best way to look at the userID and email and delete the
duplicates?

Thanx,
Wm

[J.O. Aho]

Re: duplicates in mySQL - how can I remove?

Wm wrote:[color=blue]
> I have a table of users in mySQL that appears to have a lot of duplicates.
> What's the best way to look at the userID and email and delete the
> duplicates?[/color]


Create a new table for the user list, but be sure to make the columns for
UserID to be UNIQUE (see
http://www.mysql.com/doc/en/constrai...mary_key.html), then copy from the
old table to the new, when you done that, you remove the old one and rename
the new one to the old ones name (not sure if alter table did manage this).


//Aho

[stephan beal]

Re: duplicates in mySQL - how can I remove?

J.O. Aho wrote:[color=blue]
> Create a new table for the user list, but be sure to make the columns for
> UserID to be UNIQUE (see
> http://www.mysql.com/doc/en/constrai...mary_key.html), then copy from
> the old table to the new, when you done that, you remove the old one and
> rename the new one to the old ones name (not sure if alter table did
> manage this).[/color]

But be aware that a copy like this one will fail because of the duplicated
primary keys.

IMO the simplest way may be (depending on the exact structure of the db):

mysqdump --add-drop-table mydb mytable > foo
emacs foo
<remove/rename to suit>
mysql mydb < foo


--
----- stephan beal
Registered Linux User #71917 http://counter.li.org
I speak for myself, not my employer. Contents may
be hot. Slippery when wet. Reading disclaimers makes
you go blind. Writing them is worse. You have been Warned.

[Matt Montgomery]

Re: duplicates in mySQL - how can I remove?

$sql = "select count(email) as ct, email as count from table_1 group by
userid, email";
$result = mysql_query($sq l) or die(mysql_error ());
while($row = mysql_fetch_arr ay($result)) {
if($row['email'] > 1) {
$newsql = "delete from table_1 where email = $row['email'] limit
$row['ct']-1";
run $newsql;
}
}

This should remove all duplicates.
"Wm" <LAshooter@hotm ail.com> wrote in message
news:2WQSa.2545 98$Ho4.1585224@ news.easynews.c om...[color=blue]
> I have a table of users in mySQL that appears to have a lot of duplicates.
> What's the best way to look at the userID and email and delete the
> duplicates?
>
> Thanx,
> Wm
>
>[/color]