By cMinor


2012-01-21 19:14:28 8 Comments

I have a table in SQL Server. I would like to delete the top 1000 rows from it. However, I tried this, but I instead of just deleting the top 1000 rows it deleted all the rows in the table.

Here is the code:

delete from [mytab] 
select top 1000 
a1,a2,a3
from [mytab]

6 comments

@Martin Smith 2012-01-21 19:55:31

The code you tried is in fact two statements. A DELETE followed by a SELECT.

You don't define TOP as ordered by what.

For a specific ordering criteria deleting from a CTE or similar table expression is the most efficient way.

;WITH CTE AS
(
SELECT TOP 1000 *
FROM [mytab]
ORDER BY a1
)
DELETE FROM CTE

@Nick Chammas 2012-01-21 21:06:29

For those wondering why you can't do DELETE TOP (1000) FROM table ORDER BY column, read this: "The rows referenced in the TOP expression used with INSERT, UPDATE, MERGE, or DELETE are not arranged in any order."

@Magnus 2014-01-30 13:15:46

Does this syntax work with SQL Server 2005 also?

@Martin Smith 2014-01-30 16:02:03

@Magnus yes. Not 2000 though. It might be possible to use a derived table in 2000. I haven't an instance around to test with.

@Joe Bourne 2014-03-14 18:01:29

All you need to do is: SET ROWCOUNT 1000; DELETE FROM [MyTable] WHERE ..... thats what the ROWCOUNT function is for!

@Abacus 2014-08-08 14:48:44

I had done a slightly different way (though I think the CTE might be nicer to look at): DELETE T1 FROM (SELECT TOP 1000 * FROM [MYTAB] ORDER BY A1) T1;

@Liam 2017-06-15 14:57:59

why the leading ;?

@Martin Smith 2017-06-15 18:22:02

@Liam - just because if there is any preceding statement before the CTE this needs to be terminated with a semicolon so appending it to the front of the WITH pre-empts complaints from people that haven't done that.

@Rishi Bhatnagar 2015-01-17 11:25:13

As defined in the link below, you can delete in a straight forward manner

USE AdventureWorks2008R2;
GO
DELETE TOP (20) 
FROM Purchasing.PurchaseOrderDetail
WHERE DueDate < '20020701';
GO

http://technet.microsoft.com/en-us/library/ms175486(v=sql.105).aspx

@Hamed elahi 2015-04-14 07:21:55

It is fast. Try it:

DELETE FROM YourTABLE
FROM (SELECT TOP XX PK FROM YourTABLE) tbl
WHERE YourTABLE.PK = tbl.PK

Replace YourTABLE by table name, XX by a number, for example 1000, pk is the name of the primary key field of your table.

@Tequila 2016-01-21 18:37:21

You are effectively creating two tables from one, and then deleting where joined. It works well when you want to delete the oldest (or newest) records from a table, since you can sort them ascending first. This t-sql is accepted by Microsoft (and it's fast).

@Joe Bourne 2014-03-14 11:25:14

SET ROWCOUNT 1000;

DELETE FROM [MyTable] WHERE .....

@Joe Bourne 2014-03-17 09:46:42

When dealing with just 1000 rows, does it really matter?? If it was 100,000,000 rows then your points might be valid, but for just 1000 rows, this is by far the simplest solution proposed so far for SQL 2008.

@Oleg Dok 2012-01-21 19:27:36

May be better for sql2005+ to use:

DELETE TOP (1000)
FROM [MyTab]
WHERE YourConditions

For Sql2000:

DELETE FROM [MyTab]
WHERE YourIdField IN 
(
  SELECT TOP 1000 
    YourIdField 
  FROM [MyTab]
  WHERE YourConditions
)

BUT

If you want to delete specific subset of rows instead of arbitrary subset, you should explicitly specify order to subquery:

DELETE FROM [MyTab]
WHERE YourIdField IN 
(
  SELECT TOP 1000 
    YourIdField 
  FROM [MyTab]
  WHERE YourConditions
  ORDER BY ExplicitSortOrder
)

Thanks tp @gbn for mentioning and demanding the more clear and exact answer.

@Joachim Isaksson 2012-01-21 20:28:07

@gbn Maybe useless for you, but still that is exactly what the question is asking for.

@gbn 2012-01-21 20:30:00

@Joachim Isaksson: go and read up about TOP then come back. There is no such thing as TOP without an ORDER BY in sets. Alternatively, go and find me a canonical reference that proves me wrong... To save you searching, sqlblog.com/blogs/alexander_kuznetsov/archive/2009/05/20/… and blogs.technet.com/b/wardpond/archive/2007/07/19/…

@Oleg Dok 2012-01-22 07:43:31

@gbn No any conditions about WHICH rows to delete, so ORDER BY in subquery is useless

@Oleg Dok 2012-01-22 08:11:46

@gbn Did you mention WHERE in subquery - I filter 1000 arbitrary rows inside choosen criteria and delete then. Valid scenario? Yes. If I add ORDER BY NEWID() or whatever it changes nothing - I still delete 1000 rows filtered by choosen criteria

@Oleg Dok 2012-01-22 08:17:15

@gbn The question is: I have a table in SQL I would like to delete 1000 rows from it I tried this, But I deleted all table. Where do you see top word in the question. So, If it not stated than we need to delete top X from smth. so we assume that we should delete 1000 arbitrary rows. Sounds weird - agree, but valid scenario? - agree too

@ypercubeᵀᴹ 2012-03-14 16:51:34

@OLegDok: I see it in the question title: "delete top 1000 rows ..."

@Richiban 2014-10-13 16:09:13

@gbn In case you're looking for a valid use of TOP without ORDER BY: what brought me here is I need to delete all rows matching some criteria but, for performance reasons, I don't want it to delete more than 10,000 rows at a time. I don't care which rows it deletes, as I will run the command again at some interval until all such rows are gone.

@Iman Abidi 2014-12-06 04:15:17

important: paranthesis after top is important

@Jason Dam 2012-01-21 19:16:26

delete from [mytab]
where [mytab].primarykeyid in
(
select top 1000 primarykeyid
from [mytab]
)

@gbn 2012-01-21 20:22:54

Useless: TOP without an ORDER BY gives arbitrary rows

@Joachim Isaksson 2012-01-21 20:26:32

@gbn Maybe useless for you, but still that is exactly what the question is asking for.

@gbn 2012-01-21 20:28:32

@Joachim Isaksson: I'd downvote more if I could for arguing. Go and read up about TOP then come back. There is no such thing as TOP without an ORDER BY in sets. Alternatively, go and find me a canonical reference that proves me wrong... To save you searching, sqlblog.com/blogs/alexander_kuznetsov/archive/2009/05/20/… and blogs.technet.com/b/wardpond/archive/2007/07/19/…

@Joachim Isaksson 2012-01-21 20:40:10

@gbn I did not claim that there is any default sort order or that the query is even in any way useful, I just reminded you that the question did not ask for one so what would you suggest ordering on?

@Jason Dam 2012-01-21 21:01:15

@gbn I don't know why you're so hostile to everyone over something that is a starting point. I do not claim that my answer is the end all, it is merely a suggestion to help someone out. I think the importance is the keys that are coming back from the sub query here.

@Jason Dam 2012-01-21 21:01:49

@Bassam thanks for the edit.

@Nick Chammas 2012-01-21 21:14:10

This may be all that the asker is looking for. I would just add a note for others reading to stress that the rows deleted by such a statement are not guaranteed to be in any order.

@gbn 2012-01-21 21:33:55

@Jason Dam: because of too many upvotes for incorrect answers. OP said "TOP". Top what? Quite simple.

@Andrew Steitz 2016-08-22 15:10:34

@gbn in my case, I am trying to purge a large subset of records from a bloated table and don't care WHAT ORDER they are deleted in, just as long as they meet the criteria, but I don't want to delete ALL of them at once to prevent LOCK problems. And NO, I cannot do the "copy records I want to keep to new table, then drop current table and finally rename new table to old name" routine. Try to be a little more open minded and a little less hostile.

@Andrew Steitz 2016-08-22 15:21:05

@gbn, HOWEVER, your point is a valid one that people should take into consideration. (Credit where credit is due, even if I disagree with delivery!)

Related Questions

Sponsored Content

29 Answered Questions

47 Answered Questions

37 Answered Questions

26 Answered Questions

[SOLVED] Check if table exists in SQL Server

40 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

17 Answered Questions

[SOLVED] How can I get column names from a table in SQL Server?

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

11 Answered Questions

[SOLVED] SQL Server: How to Join to first row

Sponsored Content