DELETE Top TSQL Statement

A new addition to the DELETE command in SQL Server 2005 is the TOP statement.  The DELETE TOP does the same thing as a SELECT TOP WHERE only the TOP number of rows are deleted. 

This can be very helpful when there are duplicate rows of data present. 

DELETE TOP (1)
FROM Sales.Customer
WHERE CustomerID = 1

This would delete one of the duplicate rows for Customer number 1

Suppose somehow the whole customer table got duplicated.  I duplicated the Sales.Customer table into a tmpCustomer table.

SELECT Top 1 CustomerID, COUNT(CustomerID) AS Cnt
FROM tmpCustomer
GROUP BY CustomerID
HAVING COUNT(CustomerID) > 1

WHILE @@RowCount > 0
BEGIN
    DELETE Top (1)
    FROM tmpCustomer
    WHERE CustomerID = (SELECT Top (1) CustomerID
                        FROM tmpCustomer
                        GROUP BY CustomerID
                        HAVING COUNT(CustomerID) > 1)

END

While this worked just fine, it ran about 4 minutes for 38K rows.  Let's try the dreaded CURSOR.   Notice I can stick a variable in where the TOP () statement is.  I subtracted -1 because we don't want to delete every row.

DECLARE @cnt int, @custID as int

DECLARE dupCursor CURSOR FAST_FORWARD
FOR SELECT CustomerID, COUNT(CustomerID) AS Cnt
    FROM tmpCustomer
    GROUP BY CustomerID
    HAVING COUNT(CustomerID) > 1

OPEN dupCursor

FETCH NEXT FROM dupCursor
INTO @custID, @cnt

WHILE @@FETCH_STATUS = 0
BEGIN
    DELETE Top (@cnt-1)
    FROM tmpCustomer
    WHERE CustomerID = @custID
   
    FETCH NEXT FROM dupCursor
    INTO @custID, @cnt
END

CLOSE dupCursor
DEALLOCATE dupCursor

This ran much better at 18 seconds.

 

What did you think of this article?




Trackbacks
  • No trackbacks exist for this entry.
Comments
Page: 1 of 3
  • 1/26/2009 Shakir wrote:
    Lets assume we have a table with three columns with ID as primary key.

    To delete top 5 do this:

    delete from [tablename] where id in (select top 5 id from [tablename])
    Reply to this
    1. 7/14/2010 3M Filters wrote:
      Nicely presented information in this post, I prefer to read this kind of stuff. The quality of content is fine and the conclusion is good. Thanks for the post.
      Reply to this
    2. 7/21/2010 Motorcycle wheel chock wrote:
      It is important to note that CTEs (Common Table Expressions) are also new enhancements in t-sql with the new release of SQL Server
      Reply to this
    3. 8/20/2010 mobile recharge site wrote:
      Bilaroocom: Register & collect Bilaroo Coins & Win Lowest and Unique bid with our daily auctions, Free Online mobile recharge & Send Free SMS to any mobile in India.
      mobile recharge site
      Reply to this
  • 5/19/2009 Cuban Cigars wrote:
    Thanks for the help.
    Reply to this
    1. 7/22/2010 Cheap Calls wrote:
      Your blog is so informative … keep up the good work!!!!
      Reply to this
  • 6/15/2009 Andrei wrote:
    What could be a workaround for this in sql server 2000? :S

    The DELETE TOP (1) doesnt work here..
    Reply to this
    1. 8/3/2009 Adam Rink wrote:
      The Easiest way is DELETE <table> WHERE <col> IN (SELECT TOP 1 <col> FROM <table>

      Of course, you are probably using this because you don't have a primary key and want to get rid of duplicates.

      For that you need to INSERT INTO a temp table with an identity field, or add one to your existing table.  If you use the temp table, you can move the data to the temp table then, truncate the original table and move the data back like:

      INSERT INTO <orgTable> (<cols>
      SELECT t.<cols>
      FROM #temp t
      JOIN
       (SELECT MIN(ID) AS MinID
               FROM #temp
               GROUP BY <cols> AS Dups
      ON t.ID = Dups.MinID



          
      Reply to this
  • 7/29/2009 ankkor wrote:
    Thank you for this tutorial. I've found several similar at http://www.rapidsharemix.com , but they didn't help me much. Your article was of more help to me )
    Reply to this
    1. 5/24/2010 Milestone Search wrote:
      Good post....thanks for sharing.. very useful for me i will bookmark this for my future needed. thanks for a great source.
      Reply to this
      1. 5/24/2010 Calls India wrote:
        Easy option to get useful information as well as share good stuff with good ideas and concepts
        Reply to this
  • 8/24/2009 Manchester Escorts wrote:
    The T-SQL language in the upcoming version of SQL Server will provide more power and flexibility than previous versions. Additions and enhancements include error handling with the TRY/CATCH construct, SNAPSHOT isolation, and WAITFOR enhancements. Also important are the BULK rowset provider, common table expressions, recursive queries, PIVOT and UNPIVOT operators, and much more. This article introduces these features so readers will be ready for the next version of SQL Server.
    Reply to this
    1. 5/24/2010 IT Infrastructure management wrote:
      Valuable information and excellent design you got here! I would like to thank you for sharing your thoughts and time into the stuff you post!! Thumbs up
      Reply to this
    2. 6/14/2010 Manchester Escorts Bolton wrote:
      Great news and great post. Thanks very much!
      Reply to this
      1. 7/27/2010 London Escorts wrote:
        Thanks to all the webmasters and commenters for their time to leave their view points on this issue, if feel sooo much better for the experirnce
        Reply to this
  • 11/6/2009 auto insurance wrote:
    Thanks for the help.
    Reply to this
  • 11/27/2009 gambling methods wrote:
    I am very impressed and thankful for your Blog.I am trying to access the Oracle BI Templates through MS Word — Log In Oracle BI Publisher — Open — Open Template and select the workspace and select from the combo box - Oracle BI instead of BI Publisher, I couldn't see any of the templates with in that where as I can see in Publisher its templates.I appreciate your help.
    Reply to this
  • 12/12/2009 Utah Drug Rehab centers wrote:
    Using TOP with modification statements is yet more ambiguous than using TOP with SELECT queries since with modification, you can't identify an ORDER BY clause at all. basically you end up modifying the TOP (n) rows that SQL Server happen to contact first.
    Reply to this
  • 12/23/2009 internships canada wrote:
    Also important are the BULK rowset provider, common table expressions, recursive queries, PIVOT and UNPIVOT operators, and much more. This article introduces these features so readers will be ready for the next version of SQL Server.
    Reply to this
  • 12/24/2009 Cheap Web Hosting wrote:
    Thank you for this tutorial. I've found several similar at http://www.rapidsharemix.com , but they didn't help me much. Your article was of more help to me )
    Reply to this
  • 1/5/2010 monitor stands wrote:
    How do I build a dynamic tsql delete statement from a table?
    Reply to this
  • 1/6/2010 Business listings wrote:
    What is the criteria for getting small business loans in India?
    Reply to this
  • 1/8/2010 blackjack gaming wrote:
    How to use Linux as a Microsoft SQL Server client.its a good question so I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.
    http://www.playablackjackgame.com
    Reply to this
  • 1/10/2010 Online Dating Site wrote:
    consider the following DELETE statement: DELETE TOP (3) FROM dbo. .... like Reporting Services
    Reply to this
  • 1/12/2010 Birthday sms wrote:
    Thanks for this good information about delete TSQL statement
    Reply to this
  • 2/12/2010 Projector lamps wrote:
    Ms Sql Server 2005 is great but Sql Server 2008 has enable many features.
    Reply to this
  • 2/18/2010 business from home wrote:
    Excellent blog post, I look forward to reading more.
    Reply to this
  • 3/15/2010 Website Development UK wrote:
    Really got some thing helping , Thanks for sharing wonderful post.
    Reply to this
  • 3/22/2010 Pokervarianten wrote:
    The SQLCAT blog mentioned above is showing also the derived table solution and comparing its efficiency with using the view. Performance comparison is the crux of the blog there - the view is much more efficient. Of course, if you are deleting small amounts of records the difference is negligable. But in the case of MySpace with hundreds of millions of records logged daily the potential of saving 2/3 of I/Os and more than 50% of CPU for the delete operation is very attractive.
    Reply to this
  • 3/29/2010 oil drilling wrote:
    You really know your stuff... Keep up the good work!
    http://undiscoveredequities.com
    Reply to this
  • 4/4/2010 WEB Source wrote:
    This page added to Google cache Cached: http://google.com/search?q=cache:http://mysqlserverblog.com/2007/11/04/delete-top-tsql-statement.aspx?ref=rss&ei=AFQjCNHajN_OX0kgxzx7UGA1yBfhQ poRndfWq
    Reply to this
  • 4/12/2010 sonnerie portable wrote:
    Why didn’t I find this post earlier? Keep up the good work!
    Reply to this
    1. 4/20/2010 online adult sex dating wrote:
      Thanks for the idea man...I really agree with you.....
      Reply to this
  • 4/21/2010 Birthday Sms wrote:
    very nice..
    Reply to this
  • 4/26/2010 Mayweather vs Mosley Live wrote:
    Thanks for the information.

    Regards,
    Julie
    Mayweather vs Mosley Live Stream
    Reply to this
  • 4/26/2010 Insurance Quotes wrote:
    Great tutorial man
    Reply to this
  • 4/30/2010 Jackpots dei casino online wrote:
    It is important to note that CTEs (Common Table Expressions) are also new enhancements in t-sql with the new release of SQL Server, SQL Server 2005. So the above sql statement will not run on SQL Server 2000 databases. For more samples and definition on CTEs you can read the article titled
    Reply to this
  • 5/12/2010 Application Hosting wrote:
    Do you always write this stuff, over you head.. Great article! Tell me more, let it come.. You are master.
    Reply to this
    1. 7/29/2010 international voip service wrote:
      international voip service
      Reply to this
  • 5/13/2010 Electronics wrote:
    Hi webmaster, commenters and everybody else !!! The blog was absolutely fantastic! Lots of great information and inspiration, both of which we all need!b Keep 'em coming... you all do such a great job at such Concepts... can't tell you how much I, for one appreciate all you do!
    Reply to this
  • 5/13/2010 Printer Ink wrote:
    The beauty of these blogging engines and CMS platforms is the lack of limitations and ease of manipulation that allows developers to implement rich content and 'skin' the site in such a way that with very little effort one would never notice what it is making the site tick all without limiting content and effectiveness.
    Reply to this
  • 5/13/2010 Miami Web Design wrote:
    I came lately to your website and have been reading along. I thought I would leave my initial comment. Keep writing, cause your posts are impressive! Doesn't it take up a lot of time to keep your blog so fascinating???
    Reply to this

Page: 1 of 3
Leave a comment

 Enter the above security code (required)

 Name

 Email (will not be published)

Your comment is 0 characters limited to 3000 characters.