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

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]

This question is related to sql sql-server sql-delete

The answer is


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


SET ROWCOUNT 1000;

DELETE FROM [MyTable] WHERE .....

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.


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 

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

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.


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to sql-delete

Delete all rows with timestamp older than x days How to delete duplicate rows in SQL Server? How to delete and update a record in Hive How to write a SQL DELETE statement with a SELECT statement in the WHERE clause? How can I delete using INNER JOIN with SQL Server? How to delete multiple rows in SQL where id = (x to y) Delete many rows from a table using id in Mysql How do I delete all the duplicate records in a MySQL table without temp tables Delete with "Join" in Oracle sql Query MySQL WHERE: how to write "!=" or "not equals"?