Solved

All I Want To Do Is Delete The Top 500 Rows From A Table

Posted on 2004-08-26
5
574 Views
Last Modified: 2010-08-05
All I Want To Do Is Delete The Top 500 Rows From A Table

I thought...
DELETE TOP 500 FROM tblName

But DELETE does not support TOP

Please help!

Cheers
Paul
0
Comment
Question by:pauljrichards
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 5

Accepted Solution

by:
lemmeC earned 500 total points
ID: 11901223
Try this (replace fieldname with the name of any filed from your table):

DELETE tblName
FROM (SELECT TOP 500 * FROM tblName) AS t1
WHERE tblName.fieldname = t1.fieldname




0
 
LVL 11

Expert Comment

by:ram2098
ID: 11901297
SET ROWCOUNT 500

DELETE FROM tblName

SET ROWCOUNT 0

this will do
0
 
LVL 3

Expert Comment

by:alexpreston
ID: 11901379
Or a third method... :)

DELETE FROM tblName WHERE idcolumn IN (SELECT TOP 500 idcolumn FROM tblName)
0
 
LVL 1

Expert Comment

by:sanjeev_karnik
ID: 11901525
try out this way

DELETE FROM TBLNAME A WHERE EXISTS
(SELECT TOP 500 IDCOLUMN
 FROM TBLNAME
 WHERE IDCOLUMN = A.IDCOLUMN)
0
 

Author Comment

by:pauljrichards
ID: 11902093
Thank you all very much!!!!!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question