Marcus,
> 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?
If you need to inspect the dupes, rather than eliminate them on some
criterion without inspection, this will fetch them:
SELECT LOWER(emailaddress), ...
FROM mytable
GROUP BY LOWER(emailaddress)
having COUNT(*) > 1;
PB
>
> 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>
>
>
> --No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.392 / Virus Database: 268.5.5/333 - Release Date: 5/5/2006
>
>
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 5/8/2006

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.