How to use TOP in DELETE

Hi,

I have a large database in MSSQL 2005 (rows around 3,788,299 : size 4GB)

Two questions

1. SQL query ~ select top 100 * from [tablename], how do capture the next 100 record?

2. SQL query ~ delete top 100 from [tablename] -- MSSQL show me syntax error? How can I only delete the top 100 record?

thk.

Hi,

1. not sure about this one...
2. delete top(100) [tablename] where ........

James Steele

|||

One approach for each question:

Q1:

In SQL Server 2005, we can use ROW_NUMBER() function

SELECT ROW_NUMBER() OVER(ORDER BY id ) AS Row_Number, * FROM yourTable WHERE Row_Number<=100 -- first 100

-----

WHERE Row_Number>100 and Row_Number<=200 --100-200

Q2:

DELETE

tbl_test1WHERE IDIN(

SELECT

TOP 100 IDFROM tbl_test1ORDERBY id)|||

To select the next 100 records, try using ROW_NUMBER(). You can get details from Books Online, or the article below shows how to use it for custom paging.

http://aspnet.4guysfromrolla.com/articles/031506-1.aspx

|||

2. SQL query ~ delete top 100 from [tablename] -- MSSQL show me syntax error? How can I only delete the top 100 record?

New in SQL Server 2005, you can use TOP along with DELETE.

DELETE TOP (100) FROM yourTable

works!

But in SQL Server 2000, this one will not work.

In SQL Server 2000, use this instead:

DELETE yourTable FROM (SELECT TOP 10 * FROM yourTable) AS t1 WHERE yourTable.id=t1.id