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
Thanks for the help.
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
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
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