Hi

I'm trying t delete from my database a lot of repeated mails.

I use the following statment:

delete FROM emailsuk2 where email in (SELECT email FROM emailsuk2 GROUP BY email HAVING (COUNT( email) > 1 ))


but does not work giving me the following error:

#1093 - You can't specify target table emailsuk2 for update in FROM clause


What can be wrong?

Thanks

Your query is flawed at it's root.
Doing something like that will delete EVERY OCCURENCE of an email who is present several times in your table, not just the superfluous occurences.

I don't think you can do that without procedural language or php/asp/whatever...

You didn't specified which DB you are working on, so it may not work.
This was tested in postgresql:

delete
from emailsuk2 as em1
where 1< (
    select count(email)
    from emailsuk2 as em2
    where em1.email=em2.email
    group by email
);

Well does not wok either.

Originally Posted by josil

Well does not wok either.

Originally Posted by tripy

You didn't specified which DB you are working on, so it may not work.
This was tested in postgresql:

over to you.

Yep. I stand by my words.
The query I gave you works in postgresql 8.3 without a hitch.
Tested and verified.