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]

Wm wrote:

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?

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]

J.O. Aho wrote:

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).

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]

$sql = "select count(email) as ct, email as count from table_1 group by
userid, email";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($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" <LA*******@hotmail.com> wrote in message
news:2W**********************@news.easynews.com...

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