Delete rows: first in - first out

Hello group,

I want to delete the oldest rows of a table
with ms-access/jet-engine/ADODB,
keeping only the newest 2000 rows.

Can I do this:

"DELETE * FROM myTbl WHERE myDate NOT IN (SELECT TOP 2000 myDate
FROM myTbl ORDER BY myDate ASC)"

??


--
Evertjan.
The Netherlands.
(Replace all crosses with dots in my emailaddress)

Re: Delete rows: first in - first out

Evertjan. wrote:
> Hello group,
>
> I want to delete the oldest rows of a table
> with ms-access/jet-engine/ADODB,
> keeping only the newest 2000 rows.
>
> Can I do this:
>
> "DELETE * FROM myTbl WHERE myDate NOT IN (SELECT TOP 2000 myDate
> FROM myTbl ORDER BY myDate ASC)"
>
> ??

I think you want a descending sort (DESC, not ASC), but yes, it should work.
The only glitch is that you might retain more than 2000 records if multiple
records with the same date exist. A surer technique would be to insert the
2000 records into a temp table, delete all the records from myTbl, and
insert the records from the temp table back into myTbl.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Delete rows: first in - first out

Bob Barrows [MVP] wrote on 09 okt 2005 in
microsoft.public.inetserver.asp.db:

> Evertjan. wrote:
>> Hello group,
>>
>> I want to delete the oldest rows of a table
>> with ms-access/jet-engine/ADODB,
>> keeping only the newest 2000 rows.
>>
>> Can I do this:
>>
>> "DELETE * FROM myTbl WHERE myDate NOT IN (SELECT TOP 2000 myDate
>> FROM myTbl ORDER BY myDate ASC)"
>>
>> ??
>
> I think you want a descending sort (DESC, not ASC),

Silly mistake, I should have seen that. ASC is default, isn't it?

> but yes, it should
> work. The only glitch is that you might retain more than 2000 records
> if multiple records with the same date exist. A surer technique would
> be to insert the 2000 records into a temp table, delete all the
> records from myTbl, and insert the records from the temp table back
> into myTbl.

The exact amount does not bother me, since the date is kept up to the
minute and I expect a tolerance of 3 or 4.

Thanks.

--
Evertjan.
The Netherlands.
(Replace all crosses with dots in my emailaddress)

Re: Delete rows: first in - first out

Evertjan. wrote:
>>> "DELETE * FROM myTbl WHERE myDate NOT IN (SELECT TOP 2000 myDate
>>> FROM myTbl ORDER BY myDate ASC)"
>>>
>>> ??
>>
>> I think you want a descending sort (DESC, not ASC),
>
> Silly mistake, I should have seen that. ASC is default, isn't it?

Yes


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"