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
'[EMAIL PROTECTED]', '[EMAIL PROTECTED]' and '[EMAIL PROTECTED]'. 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
[EMAIL PROTECTED] | http://www.synchromedia.co.uk
--MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
