Delete uppercase duplicates from table
926599May 2 2012 — edited May 2 2012Hi,
I have a table that contains the following data. The problem is that I have duplicate entries. There is an entry in upper case and in INITCAP case for most of my phones (not all). I want to delete all uppercase entries where there is a lower case entry available.
T_Phones
Type | Label
- - - - - - - - - - - - -
Device | APPLE IPHONE 2G
Device | Apple Iphone 2G
Device | APPLE IPHONE 3G
Device | Apple Iphone 3G
Device | APPLE IPHONE 3GS
Device | Apple Iphone 3GS
Device | APPLE IPHONE 4
Device | Apple Iphone 4
Device | APPLE IPHONE 4S
So the table should look like the following when I am done.
T_Phones
Type | Label
- - - - - - - - - - - - -
Device | Apple Iphone 2G
Device | Apple Iphone 3G
Device | Apple Iphone 3GS
Device | Apple Iphone 4
Device | APPLE IPHONE 4S
I have tried the following but I am finding this one tricky as I am not an expert with coding. Any help would be greatly appreciated. Thanks.
This will provide all upper case entries:
select TYPE,LABEL from t_phones where TYPE='DEVICE' and label = UPPER(LABEL);
This will provide all phones that have duplicates:
select Type,LABEL from T_Phones
where LABEL IN
(SELECT UPPER(LABEL)
FROM T_Phones
GROUP BY UPPER(LABEL)
HAVING COUNT(UPPER(LABEL)) > 1)
and TYPE='DEVICE'
order by label;
Rgs,
Rob
Edited by: 923596 on 02-May-2012 03:36