MSSQL Tutorial: Delete All But 1 Duplicate Records

declare @id int, @total int

/* SELECT COUNT OF POTENTIAL DUPLICATES */
select id, count(id) from [tablename]
where [parameters] group by [column name]

/* LOOP TO CLEAN UP THE DUPLICATES */
declare aff_crsr cursor LOCAL static for
select id, count(id) from [tablename]
where [parameters] group by id

open loop_crsr
fetch from loop_crsr into @id, @total

while @@fetch_status = 0 begin
print cast(@id as varchar) + ‘ – ‘ + cast(@total as varchar) + ‘ – ‘ + cast((@total – 1) as varchar)

delete top (@total-1) from [tablename] where id=@id and [parameters]

fetch from loop_crsr into @id, @total

You may also like...

Leave a Reply