SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deleting Duplicate batches of rows


Deleting Duplicate batches of rows

Author
Message
George Damien Varkey
George Damien Varkey
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 47
Comments posted to this topic are about the item Deleting Duplicate batches of rows
totlani.ritesh
totlani.ritesh
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 24
This code does not give proper results. I tried to work on this but its really untested bad work by the developer.
George Damien Varkey
George Damien Varkey
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 47
Ritesh what is it that is not working, can you let me know.
Nick Hobbs
Nick Hobbs
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 10
I think this is a much more efficient example for deleting duplicate rows using a single query and not having to resort to using Cursors:

http://dipakjpatel.blogspot.com/2007/08/delete-duplicate-rows-using-single.html

Regards

Nick
tzara
tzara
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 75
don't need a temp table. don't need cursors.

you can remove dups in one statement.

cheers.
fhanlon
fhanlon
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3031 Visits: 2310
The code worked fine for me. There is more than one way to do this. So if you don't like this way try another but in terms of results this code works.



Francis
Nick Hobbs
Nick Hobbs
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 10
I am sure the code works fine, however the article implied that this is one situation where you have to use cursors - which are very inefficient if you can do it alternatively in a single query:

>>This code works in SQL Server 2005 and above, because it uses the delete top (N) statement. Although using a cursor is not always a good idea but there are situations where we have to use it.

I am sure the cursor method with copying the data to a temporary table is satisfactory for small sets of data. I was surprised however to see it listed as a featured article in SQL Server Central's daily newsletter. When having to regularly run a query against e.g. 1m+ records that may involve several tables, it helps to know if there are more efficient methods. I thought it was worth pointing to a more efficient method that I came across recently as this is not a situation where we have to use cursors.

Regards

Nick
irina-813672
irina-813672
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 2
A much simpler way to remove duplicates would be to use SELECT DISTINCT * from tableName
Nick Hobbs
Nick Hobbs
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 10
SELECT DISTINCT * from tableName


While this gives you a list of records without the duplicates, it does not help much in deleting the duplicates. Additional code is still need to do something to remove the duplicates from the original table which this article addresses along with the other suggestions.
blairxy
blairxy
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 5
Thank you for sharing this. It was my introduction to cursors, and I was able to adapt it to keep my input table intact (by adding top(1) to another table, instead of deleting top @cnt).

Going forward, it does appear the common table expression approach linked by Nick has more promise.

All I wanted was something like 'Ignore row errors on insert'!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search