How to delete the top 1000 rows from a table using Sql Server 2008

Question

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

User · Accepted Answer

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

User · Answer

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

User · Answer

SET ROWCOUNT 1000   DELETE FROM  MyTable  WHERE

User · Answer

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  lt   20020701   GO   http   technet microsoft com en-us library ms175486 v sql 105  aspx

User · Answer

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

User · Answer

I agree with the Hamed elahi and Glorfindel  My suggestion to add is you can delete and update using aliases       given a table bi customer actions   with a field bca delete flag of tinyint or bit     and a field bca add date of datetime    note  the  if 1 1  structure allows me to fold them and turn them on and off     declare          Nrows int   1000  if 1 1    testing the inner select    begin   select top   Nrows         from bi customer actions     where bca delete flag   1     order by bca add date end  if 1 1    delete or update or select    begin   --select bca     --update bca  set bca delete flag   0   delete bca     from         select top   Nrows             from bi customer actions         where bca delete flag   1         order by bca add date       as bca end

User · Answer

delete from  mytab  where  mytab  primarykeyid in   select top 1000 primarykeyid from  mytab

[sql] How to delete the top 1000 rows from a table using Sql Server 2008?

Examples related to sql

Examples related to sql-server

Examples related to sql-delete