Query To Delete A certain # of records

  • Thread starter Cylvia via AccessMonster.com
  • Start date
C

Cylvia via AccessMonster.com

I have the following query to delete the top 65500 records from a table
tbl_CRS

DELETE * From tbl_CRS
SELECT TOP 65500
FROM tbl_CRS
ORDER BY Name;


, but it doesn't work. It give me syntax error in the FROM statement. Can
anyone tell me why?

Thanks Much!!

Cyl
 
C

Cyl via AccessMonster.com

I tried this but get a syntax error as well. in the select statement...

DELETE tbl_CRS *
SELECT TOP 65500
FROM tbl_CRS
ORDER BY Name;
 
K

Ken Sheridan

Try using a subquery in the WHERE clause:

DELETE *
FROM tbl_CRS
WHERE Name IN
(SELECT TOP 65500 Name
FROM tbl_CRS
ORDER BY Name);

BTW I'd avoid using Name as a column name. It could easily be confused with
the Name property of objects. Use something like CustomerName, EmployeeName
or whatever is appropriate to avoid any ambiguity.

Ken Sheridan
Stafford, England
 
J

John Spencer

Also if you want to get exactly 65500 (or less) records then I would
strongly suggest that you use your table's primary key in the order by and
as the field you are matching.
 
Top