By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,864 Members | 1,279 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,864 IT Pros & Developers. It's quick & easy.

Deleting 250000+ rows at once

P: n/a

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.


Jun 27 '08 #1
Share this Question
Share on Google+
2 Replies

P: n/a
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 :-)


The Frog
Jun 27 '08 #2

P: n/a
jodleren <so*****@hot.eewrote:
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


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
Tony's Microsoft Access Blog -
Jun 27 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.