SQLServerCentral.com / SQL Server 2012 / SQL 2012 - General / DELETE FIRST 1000 ROWS / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 02 Aug 2015 14:25:40 GMT20RE: DELETE FIRST 1000 ROWShttp://www.sqlservercentral.com/Forums/Topic1556195-2799-1.aspxOnce you determine what determines the first rows, I like using this approach:[code="sql"]WITH cte AS ( SELECT TOP 1000 FROM TableName ORDER BY EntryDate ASC)DELETE FROM cte;[/code]This gives the added benefit of being able to replace the DELETE with a SELECT so you can see the rows that are going to be deleted before you actually delete them. However, the fundamental question of the order remains. Since the ONLY way to guarantee the order is by using an ORDER BY clause, don't skip this step.Mon, 31 Mar 2014 12:03:27 GMTEd WagnerRE: DELETE FIRST 1000 ROWShttp://www.sqlservercentral.com/Forums/Topic1556195-2799-1.aspx[quote][b]Jeff Moden (3/31/2014)[/b][hr]While some of the methods posted are ok, there's still the fundamental problem...[quote][b]shashianireddy (3/29/2014)[/b][hr]I WANT DELETE FIRST 1000 ROWS IN SQL TABLE ............................[/quote]... of not knowing what the first 1000 rows should be.[/quote]ooops :blush: (thundering voice: read the whole question....;-)Mon, 31 Mar 2014 11:39:12 GMTEirikur EirikssonRE: DELETE FIRST 1000 ROWShttp://www.sqlservercentral.com/Forums/Topic1556195-2799-1.aspxWhile some of the methods posted are ok, there's still the fundamental problem...[quote][b]shashianireddy (3/29/2014)[/b][hr]I WANT DELETE FIRST 1000 ROWS IN SQL TABLE ............................[/quote]... of not knowing what the first 1000 rows should be.Mon, 31 Mar 2014 10:25:29 GMTJeff ModenRE: DELETE FIRST 1000 ROWShttp://www.sqlservercentral.com/Forums/Topic1556195-2799-1.aspxThis can be done using sub-queryUsing TOP[code="sql"]DECLARE @BATCH_SIZE INT = 10;DELETE XFROM (SELECT TOP (@BATCH_SIZE) [COLUMN]FROM [TABLE_NAME] MORDER BY [COLUMN] DESC) AS X[/code]Using OFFSET-FETCH (2012)[code="sql"]DECLARE @BATCH_SIZE INT = 10;DELETE XFROM (SELECT [COLUMN]FROM [TABLE_NAME] MORDER BY [COLUMN] DESCOFFSET 0 ROWSFETCH FIRST (@BATCH_SIZE) ROWS ONLY) AS X[/code]Mon, 31 Mar 2014 05:33:22 GMTEirikur EirikssonRE: DELETE FIRST 1000 ROWShttp://www.sqlservercentral.com/Forums/Topic1556195-2799-1.aspx[quote][b]GilaMonster (3/31/2014)[/b][hr][quote][b]Lynn Pettis (3/29/2014)[/b][hr]DELETE TOP (1000) FROM MyTable ORDER BY yourOrderingColumn[/quote][color="#FF0000"]Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'ORDER'.[/color]Amusingly enough, while updates and deletes can take a TOP, they can't have an ORDER BY clause.To do a delete of the first x rows, ordered by something, this is what's needed:DELETE FROM MyTable WHERE UniqueColumn IN (SELECT TOP(n) UniqueColumn FROM MyTable ORDER BY ColumnWhichDeterminesOrder)[/quote]Thanks, don't know what I was thinking. Must be these 7 x 12+ hour days, not always thinking straight toward the end of the day.Mon, 31 Mar 2014 05:05:13 GMTLynn PettisRE: DELETE FIRST 1000 ROWShttp://www.sqlservercentral.com/Forums/Topic1556195-2799-1.aspx[quote][b]Lynn Pettis (3/29/2014)[/b][hr]DELETE TOP (1000) FROM MyTable ORDER BY yourOrderingColumn[/quote][color="#FF0000"]Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'ORDER'.[/color]Amusingly enough, while updates and deletes can take a TOP, they can't have an ORDER BY clause.To do a delete of the first x rows, ordered by something, this is what's needed:DELETE FROM MyTable WHERE UniqueColumn IN (SELECT TOP(n) UniqueColumn FROM MyTable ORDER BY ColumnWhichDeterminesOrder)Mon, 31 Mar 2014 02:15:10 GMTGilaMonsterRE: DELETE FIRST 1000 ROWShttp://www.sqlservercentral.com/Forums/Topic1556195-2799-1.aspxYou can use row number () to create ids for all the rows and then based on any column order you can delete 1000 rows:DELETE FROM (SELECT ROW_NUMBER() OVER (ORDER BY Empcode) AS Row, Name, Address, Idno FROM Table_name) AS tablenameWHERE Row&lt;=1000Mon, 31 Mar 2014 01:11:50 GMTgstarsainiRE: DELETE FIRST 1000 ROWShttp://www.sqlservercentral.com/Forums/Topic1556195-2799-1.aspx[quote][b]shashianireddy (3/29/2014)[/b][hr]I HAVE TABLE LIKE BELOW 3000 ROWSNAME ADDRESS IDNO EMPCODE SVR HYD 123456 9876SGH HSD 98765 987654....I WANT DELETE FIRST 1000 ROWS IN SQL TABLE ............................PLZ WRITE QUARIE ..........[/quote]In SQL Server there really is no concept of first 1000 rows. So first question, how are you going to determine what are the first 1000 rows? By what column are you going to order the data and are you ordering it in ascending or descending order?Here is a start:DELETE TOP (1000) FROM MyTable ORDER BY yourOrderingColumnSat, 29 Mar 2014 07:56:42 GMTLynn PettisDELETE FIRST 1000 ROWShttp://www.sqlservercentral.com/Forums/Topic1556195-2799-1.aspxI HAVE TABLE LIKE BELOW 3000 ROWSNAME ADDRESS IDNO EMPCODE SVR HYD 123456 9876SGH HSD 98765 987654....I WANT DELETE FIRST 1000 ROWS IN SQL TABLE ............................PLZ WRITE QUARIE ..........Sat, 29 Mar 2014 02:21:07 GMTshashianireddy