Thread

Hello

We use Mailing List module to manage newsletter subscribers. There are about 30 duplicate emails. Anyone found an easy way to remove these?

Thought of:

1:Exporting and fixing in BBEdit then reimporting

2: Doing this using SQL Manager somehow

Thanks

Hamish, when you say there are duplicate email addresses, how did they get there? Normally if you attempt to add an email address that is a duplicate, you receive an error message.

The error messages appeared OK. Something along the lines of ‘There were three duplicates’.

I’m afraid it was compiled from several large lists kept by different people and there was no easy way to compare them all before entry.

So are you just wanting confirmation that the emails in the list aren’t duplicated?

Do you just have one mailing list?

Hi

I have just one mailing list in EE but there are several hundred email addresses on it, including about 30 duplicates.

As the addresses are not displayed in alphabetical order it’s not easy to identify the duplicates, so i’m wondering whether there is a way to identify and remove them either using EE or whether a SQL query would do the job.

Thanks

You can do a series of queries to find that information.

Here’s one:

select distinct email from exp_mailing_list order by email asc

-and-

select email from exp_mailing_list order by email asc

See if the counts are the same. If they are, then there are no duplicates.

Does this make sense?

Hi Sue

That makes sense. However, the setup here is that i already know there are around 30 duplicates on the list, but i just need a method of identifying the duplicate addresses and getting rid of them.

Since this is more along the lines of cleaning up an import and the actual module is working as intended, I’m going to move this to how to.  You might consider exporting the mailing list and using some text editor that can search for duplicate strings, cleaning them up then re-importing. That may be easiest.

Hmm, try:

delete from exp_mailing_list where email in (select email from exp_mailing_list group by email having count(email) > 1)

As always backup the table first in case gremlins get in there.

Hi

Thanks for the SQL tip

It’s now dawning on me that i might have completely misinterpreted they EE error message in the first place and that there might not be a problem here at all. The emails have been added in a few batches of roughly a couple of hundred at a time, and, for example, on seeing the message:

Total subscribed emails:  200
Total duplicate emails:  20

I took this to mean 200 were added and 20 of these were duplicates AND they have been added as well. What it actually means is ‘you submitted 210 emails, 200 were added and 20 were not because they already exist’ which is, in fact, completely intuitive and ideal functionality.

So, er, apologies for the timewasting ...

However, as this has now been moved to the ‘How To’ thread’, for those about to import a chaotic list, i can recommend BBEdit’s remove duplicate lines function.