[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