Really nasty duplicates question. XPERT NEEDED!
-
Hi All,
I'm a bit stuck as to the best way to handle the following....
Room_id Room_name
1 StaffRoom
2 StaffRoom
3 StaffRoom
I can easily delete these with a max() function, as per the usual solutions but I want to add a letter to the end of them instead and keep the records...
Room_id Room_name
1 StaffRoom
2 StaffRoomA
3 StaffRoomB
Not sure what to do here, the sql for getting the duplicates is below (may be of some use!).
declare @tableofduplicates table
(room_id int, min_room_id int, room_name varchar(200), number_of_duplicates int)
insert into @tableofduplicates
(room_id, min_room_id, room_name, number_of_duplicates)
select r.room_id, min_room_id, r.room_name, number_of_duplicates from sims.sims_room r
-- this join gets the duplicates
inner join
(select room_name
from sims.sims_room
group by room_name
having count(room_name) > 1) c
on c.room_name = r.room_name
-- this join gets the smallest room_id's
left outer join
( select room_name, min(room_id) as min_room_id, count(room_name) as number_of_duplicates
from sims.sims_room
group by room_name
having count(room_name) > 1 ) b
on b.room_name = r.room_name
where min_room_id != r.room_id
-
Update d Set Room_Name = Case when Cnt is null or Cnt = 0 then Room_name
Else Room_name + Char(asccii('A') + Cnt)) end
from
sims d
join
(select id, Room_name ,(Select Count(*)
from sims s2
where s2.Room_name = s1.Room_name
and s1.id < s2.id) Cnt
From
sims s1
) s
on d.id =s.id
HTH
* Noel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply