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.
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.
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
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
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
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
Thanks for the help.
Reply to this
Your blog is so informative … keep up the good work!!!!
Reply to this
What could be a workaround for this in sql server 2000? :S
The DELETE TOP (1) doesnt work here..
Reply to this
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>
ON t.ID = Dups.MinID
Reply to this
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
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
Easy option to get useful information as well as share good stuff with good ideas and concepts
Reply to this
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
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
Great news and great post. Thanks very much!
Reply to this
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
Thanks for the help.
Reply to this
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
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
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
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
How do I build a dynamic tsql delete statement from a table?
Reply to this
What is the criteria for getting small business loans in India?
Reply to this
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
consider the following DELETE statement: DELETE TOP (3) FROM dbo. .... like Reporting Services
Reply to this
Thanks for this good information about delete TSQL statement
Reply to this
Ms Sql Server 2005 is great but Sql Server 2008 has enable many features.
Reply to this
Excellent blog post, I look forward to reading more.
Reply to this
Really got some thing helping , Thanks for sharing wonderful post.
Reply to this
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
You really know your stuff... Keep up the good work!
http://undiscoveredequities.com
Reply to this
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
Why didn’t I find this post earlier? Keep up the good work!
Reply to this
Thanks for the idea man...I really agree with you.....
Reply to this
very nice..
Reply to this
Thanks for the information.
Regards,
Julie
Mayweather vs Mosley Live Stream
Reply to this
Great tutorial man
Reply to this
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
Do you always write this stuff, over you head.. Great article! Tell me more, let it come.. You are master.
Reply to this
international voip service
Reply to this
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
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
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