Remove duplicates 1

[dbalpine]

This should be simple for most of you...I want to remove duplicate e-mail addresses in my email field of my table called email_pft. so...

DELETE FROM 'email_pft' WHERE email= ????

that if an address exists more than once delete all other instances but the first one??

 

[sleipnir214]

It's not as simple as that.

To do the deletion in a single query would require the use of a subquery. MySQL only supports subqueries in versions 4.1.x and newer. However, those versions of MySQL are alpha-revision code.

You can do it in two stages pretty simply with older versions of MySQL if you have a numerical ID column for each record. First, you get a list of IDs of rows for duplicated entries by joining the table to itself. Something like:

select t2.id_column from
		email_pft as t1
	left join
		email_pft as t2
	on t1.email = t2.email
WHERE
	t2.email is not null and
	t1.id_column < t2.id_column

If you have a programming languate (PHP, perl, etc) you could then loop through that return, deleting rows by IDs.

If you don't have a programming language, you should be able to copy-and-paste that return into a text editor and transform it into a list of IDs to be deleted in a query like:

DELETE FROM email_pft WHERE id_column in (<list of IDs>)

For that matter, you could also use a programming language to perform the transformation on the return.



If you are running MySQL 4.1.x, you can probably issue a single query something like:

delete from email_pft where id_column in
(
	select t2.id_column from
			email_pft as t1
		left join
			email_pft as t2
		on t1.email = t2.email
	WHERE
		t2.email is not null and
		t1.id_column < t2.id_column
)

I say "probably" because I don't have a version of MySQL 4.1.x to test the query on.


If you don't have an id column, it'll be pretty tough finding duplicates.


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!