[Babase] removing records from samples and associated tables

Catherine Markham babase@www.eco.princeton.edu
Fri, 28 Oct 2005 16:35:42 -0400


Hi,

I wasn't quick enough (was just writing a response when Karl's popped 
up).  I definitely didn't do all my conversions corrections by hand - I 
think I'd still be doing them if that was the case.  FoxPro does have an 
expression builder pop-up window for deleting rows (look on the top 
toolbar under "table" and then go to "delete records").  If I'm 
remembering right, I played around with that in some dummy table to test 
the kind of commands it created... I think (but no promises - you'll 
have to recheck) these commands just flagged the rows for deletion.  You 
had to then "remove deleted rows" to actually get them out of the 
database table.

Sorry I don't remember more specifics, but hopefully it is encouraging 
to know that I don't think any programs have to be created and I don't 
think you need to do it all by hand.

And Karl's point about the order of deletion is really good - I was 
worried about making that mistake when I first got going with the 
conversion error corrections.  Not sure this helps (or maybe it is 
obvious), but here's what I did:

For each major deletion (involving a number of rows - not something I 
did by hand), I first created a table of those rows that should be 
deleted.  I saved this table (still have it just in case we need to go 
back and add back in data - hope not, but I was trying to be super 
careful).  I then jotted down the number of rows in that table, did the 
deletion command on the database table, removed deleted rows, checked 
the number of rows in the new database table (obviously should equal 
original minus the number of rows in the query table), and finally reran 
the query.  If the new database table had the expected number of rows 
and rerunning the query resulted in a blank table, I reasoned everything 
had worked fine.

Sorry to be so wordy - hope that helps,
Catherine


Karl O. Pinc wrote:
> 
> On 10/28/2005 02:34:47 PM, Leah Gerber wrote:
> 
>> Karl,
>>
>> I am wondering if there is an easy way to remove all the records  
>> associated with one sname in SAMPLES. There are 163 rows in samples  
>> that have DAS (a male) as an sname. Is there a way to remove the  
>> associated rows from JPSAMPS, FPSAMPS, ADLIBS, PARTS, and INTERACT in  
>> a more automated fashion? I can do it by hand but it will take quite  
>> a while.
> 
> 
> Well yes, but I think it would involve writing a program.  Which
> would probably take longer than doing it by hand.  You can look
> up the foxpro DELETE command and see if it will let you add a WHERE
> clause to write something like:
> 
> DELETE FROM JPSAMPS WHERE SAMPLES.SNAME = 'DAS' AND
>   JPSAMPS.SID = SAMPLES.SID
> 
> but as I recall foxpro won't do that.
> 
> Try also:
> 
> DELETE FROM JPSAMPS WHERE SID IN (SELECT SAMPLES.SID
>   FROM SAMPLES WHERE SNAME = 'DAS')
> 
> 
> (Be careful not to delete SAMPLES before JPSAMPs, etc.)
> 
> Karl <kop@meme.com>
> Free Software:  "You don't pay back, you pay forward."
>                  -- Robert A. Heinlein
> 
> _______________________________________________
> Babase mailing list
> Babase@www.eco.princeton.edu
> http://www.eco.princeton.edu/mailman/listinfo/babase