468,107 Members | 1,312 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,107 developers. It's quick & easy.

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
Jul 16 '05 #1
3 7673
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

Jul 16 '05 #2
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.

Jul 16 '05 #3
$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

Jul 16 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Tom Cunningham | last post: by
5 posts views Thread by Papajo | last post: by
3 posts views Thread by bellefy | last post: by
6 posts views Thread by M B HONG 20 | last post: by
7 posts views Thread by vsgdp | last post: by
Thekid
3 posts views Thread by Thekid | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.