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

sql sql-server sql-delete
edgarmtze picture edgarmtze · Jan 21, 2012 · Viewed 253k times · Source

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]

Answer

Martin Smith picture Martin Smith · Jan 21, 2012

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

Related questions

How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
SQL DELETE with INNER JOIN

There are 2 tables, spawnlist and npc, and I need to delete data from spawnlsit. npc_templateid = n.idTemplate is the only thing that "connect" the tables. I have tried this script but it doesn't work. I have tried this: DELETE …

Read More
How to Delete Records NOT IN

Hi I have the following SQL Query which gives me Scheme_Id which exist both in ProjectSchemes and Schemes table. I want to delete all records from Schemes table which have no record to ProjectSchemes table. How can I do …

Read More