Solved

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

Posted on 2004-08-26
5
575 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

729 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