Deleting 250000+ rows at once

[jodleren]

Hi!

In a system, which collects data and the summarises it, we collect a
large amount of data.
Now, there seems to be a problem, when we try to delete just 16000
rows (entire table btw) or some 250000 rows in another table. My test
table is totally 94 MB, but larger have been seen :-)

The error message reads: "Record is deleted" (16k rows table) or "The
search key is not found in any record" (250000 rows, leaving some rows
to survive)

Solution: probably the solution is to delete data patially. I tried to
do that.

Deleting is done as: delete from whatever where date < now-
some_days_say_3 and source_id=56

It loops for each source, but that it not enough.
We do not know how old data we have, but a solution could be to read
in min(date), then do it one day at a time. But is there a better way
to do it in one sql, one step?

We use Delphi and ADO connection through ODBC.

WBR
Sonnich

[The Frog]

Re: Deleting 250000+ rows at once

Hi There,

Have you considered generating a batch number for each days data
imports, then having a second table with the batch number and the date
it was imported? You could then identify the rows very easily. Once
you can identify the rows you can do whatever you want with them :-)

Cheers

The Frog

[Tony Toews [MVP]]

Re: Deleting 250000+ rows at once

jodleren <sonnich@hot.ee wrote:

"The
>search key is not found in any record" (250000 rows, leaving some rows
>to survive)

This sounds very much like a, or a few, records are corrupted.

See the Corrupt Records in a Microsoft Access MDB page at my website

http://www.granite.ab.ca/access/corruption/corruptrecords.htm

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/