Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DELETE FIRST 1000 ROWS Expand / Collapse
Author
Message
Posted Saturday, March 29, 2014 2:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 6, 2014 12:03 AM
Points: 30, Visits: 119
I HAVE TABLE LIKE BELOW 3000 ROWS

NAME ADDRESS IDNO EMPCODE
SVR HYD 123456 9876
SGH HSD 98765 987654
.
.
.
.


I WANT DELETE FIRST 1000 ROWS IN SQL TABLE ............................

PLZ WRITE QUARIE ..........
Post #1556195
Posted Saturday, March 29, 2014 7:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 23,033, Visits: 31,556
shashianireddy (3/29/2014)
I HAVE TABLE LIKE BELOW 3000 ROWS

NAME ADDRESS IDNO EMPCODE
SVR HYD 123456 9876
SGH HSD 98765 987654
.
.
.
.


I WANT DELETE FIRST 1000 ROWS IN SQL TABLE ............................

PLZ WRITE QUARIE ..........


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 yourOrderingColumn



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1556215
Posted Monday, March 31, 2014 1:11 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:02 AM
Points: 411, Visits: 129
You 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 tablename
WHERE Row<=1000
Post #1556354
Posted Monday, March 31, 2014 2:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 42,462, Visits: 35,525
Lynn Pettis (3/29/2014)

DELETE TOP (1000) FROM MyTable ORDER BY yourOrderingColumn


Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ORDER'.


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)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1556376
Posted Monday, March 31, 2014 5:05 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 23,033, Visits: 31,556
GilaMonster (3/31/2014)
Lynn Pettis (3/29/2014)

DELETE TOP (1000) FROM MyTable ORDER BY yourOrderingColumn


Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ORDER'.


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)


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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1556419
Posted Monday, March 31, 2014 5:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 1,294, Visits: 3,729
This can be done using sub-query

Using TOP
DECLARE @BATCH_SIZE INT = 10;
DELETE X
FROM (
SELECT TOP (@BATCH_SIZE) [COLUMN]
FROM [TABLE_NAME] M
ORDER BY [COLUMN] DESC
) AS X


Using OFFSET-FETCH (2012)
DECLARE @BATCH_SIZE INT = 10;
DELETE X
FROM (
SELECT [COLUMN]
FROM [TABLE_NAME] M
ORDER BY [COLUMN] DESC
OFFSET 0 ROWS
FETCH FIRST (@BATCH_SIZE) ROWS ONLY
) AS X







Post #1556426
Posted Monday, March 31, 2014 10:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 36,767, Visits: 31,223
While some of the methods posted are ok, there's still the fundamental problem...

shashianireddy (3/29/2014)
I WANT DELETE FIRST 1000 ROWS IN SQL TABLE ............................


... of not knowing what the first 1000 rows should be.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1556583
Posted Monday, March 31, 2014 11:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 1,294, Visits: 3,729
Jeff Moden (3/31/2014)
While some of the methods posted are ok, there's still the fundamental problem...

shashianireddy (3/29/2014)
I WANT DELETE FIRST 1000 ROWS IN SQL TABLE ............................


... of not knowing what the first 1000 rows should be.


ooops (thundering voice: read the whole question....
Post #1556625
Posted Monday, March 31, 2014 12:03 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 3,968, Visits: 2,977
Once you determine what determines the first rows, I like using this approach:

WITH cte AS (
SELECT TOP 1000
FROM TableName
ORDER BY EntryDate ASC)
DELETE FROM cte;

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.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1556634
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse