I would run this query:
SELECT
*
FROM mytable
WHERE LOWER(emailaddress) IN
(SELECT
LOWER(emailaddress)
FROM mytable
GROUP BY 1
HAVING COUNT(emailaddress) > 1)
This would show all duplicate emails, I would use the info this displays
to choose which records to change/keep/delete.
May not be the best way, but it would work.
Chris
Marcus Bointon wrote:
> I just noticed that a key field (emailaddress) in my db is case
> sensitive when it should not have been, so now I've got a bunch of
> what are effectively duplicate records. I'm having trouble picking
> them out so I can manually merge/delete them before changing the
> collation on the field to be case insensitive.
>
> SELECT * FROM mytable group by lower(emailaddress) having
> count(emailaddress) > 1
>
> This is ok, but it only shows me the records with lower case addresses
> (I can't tell which case version is the correct one without looking at
> them) when I want to see the records with all cases, that is all of
> 'joe@stripped', 'Joe@stripped' and 'JOE@stripped'. I'm
> confusing myself with the case sensitivity and self-references!
>
> I think there are about 45 duplicates out of about 200,000.
>
> How can I find these pesky things?
>
> thanks,
>
> Marcus
> --Marcus Bointon
> Synchromedia Limited: Putting you in the picture
> marcus@stripped | http://www.synchromedia.co.uk
>
>
> --MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
