Query To Delete A certain # of records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cylvia - 20 Mar 2006 22:01 GMT

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

Reply to this Message

Cyl - 20 Mar 2006 23:03 GMT

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;

>I have the following query to delete the top 65500 records from a table
>tbl_CRS
[quoted text clipped - 10 lines]
>
>Cyl

Reply to this Message

Ken Sheridan - 20 Mar 2006 23:46 GMT

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

> I tried this but get a syntax error as well. in the select statement...
>
[quoted text clipped - 17 lines]
> >
> >Cyl

Reply to this Message

John Spencer - 21 Mar 2006 13:19 GMT

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.

> Try using a subquery in the WHERE clause:
>
[quoted text clipped - 36 lines]
>> >
>> >Cyl

Reply to this Message