need help deleting records in a table!

[darrontrask]

Let me start out by saying I know very little about programming. I was handed the job of deleting about 35000 records stored in our database from 10 years ago until present. I need to delete all the records prior to 2002. i can go through manually and tag them all but that will take a loooong time. Is there an easy way to do this? Thank you all in advance for any help you can give me.

 

[Mike Gagnon]

Do you have a date field (most likely you do) you can go by? If so you could use

SELECT myTable
SET DATE MDY
DELETE FOR YEAR(myDatefield) <= YEAR(CTOD("01/01/2002"))

Make a back up first. !

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[baltman]

You'll also need a 'PACK' to permanently delete the records. I suggest you read VFP's HELP for these commands. It is quite good and often contains sample code.

Brian

 

[mkrausnick]

Forgive the basic level, but you said you're unfamiliar with programming, so maybe we should back up a bit:

First, these are DBF files, right, not a SQL Server or Oracle database? If it's a client server database, you won't necessarily be working in VFP, but in the environment of the database. The procedure may vary slightly depending on the environment.

Second, you have access to a full version of Visual Foxpro, so you can access the VFP development environment, i.e., get a VFP command window, right?

Third, as Mike G indicated, you need a field in the record to identify the records that are older than 2002. If that's a date field, Mike's procedure is correct. If it's another type of field, like a sequential ID field or a part number, then you have to adjust the instructions accordingly.

The thing about DBF files that's different from most other formats is that the records aren't physically deleted when you hit the DELETE key. Even if you don't see them any more, they're still there and taking up space. They are physically removed from the table only when you issue the PACK command. That's where Brian is coming from. You need EXCLUSIVE access to pack the file, unlike when you're just marking the records for deletion.

So: Tell us a little more about your set-up, and we then can give you the correct instructions.


Mike Krausnick
Dublin, California

 

[darrontrask]

Sorry for the long delay in my response, I have been fighting fires in the other departments of my company. Yes these are DB files. When Foxpro opens up I have the command window. Right now I connect through a shortcut directly off the computer that has the Foxpro installed. I have access to the main PC if I need to go there. Yes we do have a date field to choose the records to delete from. I appreciate all the reponses so far. I do need to permently delete these files as they are causing us to have duplicate pallet and order numbers.

 

[mkrausnick]

OK, to consolidate the various comments into one set of instructions:

At the VFP command prompt,

use mytable exclusive              && Opens the table with exclusive access
copy to backuptable with cdx       && Creates a backup in case this doesn't work like you want
delete for year(datefield) < 2002  && Deletes records prior to 2002
pack                               && Removes deleted records from the table
use                                && Closes the table

Notes:
1. Everyone must be out of any applications that use the table while you perform this operation
2. <mytable> is the table you are purging
<datefield> is the field containing the date to be tested.

Hope this helps.

Mike Krausnick
Dublin, California

 

[darrontrask]

Mike
I used the code but it erased all my records not just the ones prior to 2002. Now I guess I need to put the backed up data back into that table and try again. How do I do this? I really need to get the records back that were created after 2002.

 

[alvechurchdata]

Mike's code would only have deleted the records which had a date earlier than 2002. Are you sure that the field you were testing had the right years in it? Fox knows all about Leap Years and the Millenium but if it's a very old database you might find it has 1902 instead.

One mor ething. Delete the PACK command before you run the the code again. That way you can check that the correct records have indeed been marked before you take the final step of deleting them.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[darrontrask]

How do I put the records from the backup table back into the original table? And can I add records to the original table before I restore the old ones?

 

[Mike Gagnon]

Delete all records in the current table and use:

select mycurrent table
APPEND FROM myBackup.dbf

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[Mike Gagnon]

One suggestion, in order to double check yourself, set a filter on your table on year and see what you get.

select myCurrent.dbf 
SET FILTER ON YEAR(myDatefield)<=2002
BROWSE

If you see only the records you want to delete then (keep your filter on) and use

DELETE ALL

This will only delete the records within the filter.



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[TamarGranor]

Follow Mike's advice, but make it:

SET FILTER TO YEAR(myDateField) <= 2002

not SET FILTER ON

Tamar

 

[darrontrask]

Thanks MIKE! that worked to restore my data.

When I apply the filter to the table it still grabs every record. I have tried to filter based on two different date columns and both show every record. maybe i am missing something. I have been entering our database and returning to foxpro to get to the command window. Is there something else I should be doing?

I really appreciate everyone help and advice it has given me a better understanding of how FP works.

 

[TamarGranor]

From the Command Window, open your table and issue:

LIST STRUCTURE TO FILE Stru.TXT

Then post the contents of that file here, telling us which fields are the ones you're trying to filter on.

Tamar

 

[darrontrask]

I hope this is what you needed. The fields that I need to sort by are the "wipdate" and "shipdate". I would like to delete all the records prior to 2005. Also if I delete them and not pack them will I still get duplicate entries? Currently when I search I get the first record(the oldest) and cannot access the newest records.

Thanks again for all you help!

Structure for table: \\OFFICE\C\APPLCARB\DATA\PALLHIST.DBF
Number of data records: 46615
Date of last update: 02/01/06
Code Page: 1252
Field Field Name Type Width Dec Index Collate Nulls
1 PRODUCT Character 12 Asc Machine No
2 PRODUCTGRP Character 3 No
3 PALLET Character 12 Asc Machine No
4 GROSSWGT Numeric 5 No
5 NETWGT Numeric 5 No
6 DENSITY Numeric 6 2 No
7 SIZE1 Character 6 No
8 PERCENT1 Numeric 6 2 No
9 SIZE2 Character 6 No
10 PERCENT2 Numeric 6 2 No
11 SIZE3 Character 6 No
12 PERCENT3 Numeric 6 2 No
13 SIZE4 Character 6 No
14 PERCENT4 Numeric 6 2 No
15 SIZE5 Character 6 No
16 PERCENT5 Numeric 6 2 No
17 SIZE6 Character 6 No
18 PERCENT6 Numeric 6 2 No
19 FIXDCARBON Numeric 5 2 No
20 LOI Numeric 5 2 No
21 MOISTURE Numeric 5 2 No
22 PALLETSIZE Character 1 No
23 CONTAINER Character 2 No
24 ASSAYIN Character 9 No
25 RECVDATE Date 8 No
26 WIPDATE Date 8 No
27 USEDDATE Date 8 No
28 PRODDATE Date 8 No
29 ASSAYOUT Date 8 No
30 SHIPDATE Date 8 No
31 STATUS Character 3 No
32 WLSECTION Character 10 No
33 WLROW Character 3 No
34 WLSPACE Character 3 No
35 WLPOSITION Character 6 No
36 ORDERNO Character 12 Asc Machine No
37 LINENO Character 3 No
38 LASTSECTN Character 3 No
39 LASTROW Character 3 No
40 LASTSPACE Character 3 No
41 LASTPOSITN Character 6 No
** Total ** 246

 

[JimWWinter]

Are you setting your filter while you have an index set to the date field you're filtering? If so, you may have a corrupted index. Try setting the filter with no active index and see what the results look like.

Good luck,
Jim

 

[mreigler]

I'd go back to what Geoff Franklin posted earlier and verify you have 2002 and not 1902.

This should give you the min and max values for your date fields. Set Century on will make sure you see the 4 digit year instead of the last two only.

SET CENTURY ON

SELECT MIN(wipdate) AS Min_Wip, ;
MAX(wipdate) AS Max_Wip,
MIN(shipDate) AS Min_Ship,
MAX(shipdate) AS Max_SHip
FROM pallhist

Mike Reigler
Melange Computer Services, Inc

 

[darrontrask]

I got the records deleted that i need to remove. Thank you all for your help. This has given me a little better understanding of how foxpro works.