Rory O'Connor writes:
> I want to leave one record behind, but delete all other records that
> have the same e-mail address. Is there a standard query for this or do
> I need to write a script that will do it?
Getting a list of the duplicate rows is simple enough, but
deleting them requires a bit of scripting as far as I know.
Using mysql's oddly extended GROUP BY:
SELECT id, email, COUNT(*) FROM mytable GROUP BY email
HAVING COUNT(*) = 1
Then just make a comma-separated list of the ids and do a
DELETE FROM mytable WHERE id NOT IN(id,id,id,...)
Then add a unique index to the email column as you should've
done in the first place if you didn't want duplicates. :-)
//C
--
Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280
[EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/
Amiga user since '89, and damned proud of it too.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
