MSSQL Tutorial: Delete All But 1 Duplicate Records

Leave a comment Standard

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s