1) I want to delete all parent records with FieldA= valueA.  So far, so good

2) Then, I want to delete all ChildA records whose parents I just deleted.

3)  Then, since ChildA was parent to records in several different
tables, I need to delete all records that were children of ChildA.

I dont particularly need to be elegant; it could be done with several
different stages.  But I dont know how to code
steps 2 and 3.

TIA,
Jerry

Jerry,

I came across a similar requirement a little while ago.

Delete from DetailTable
left outer join MasterTable on (MT.Key = DT.Key)
where MT.Key is null

(off the top of my head. Test it first

HTH

Adam.

Adam,

<< (off the top of my head. Test it first >>

Looks good to me - it will delete all children with no parent.

--
Tim Young
Elevate Software
www.elevatesoft.com

Jerry


Adam's approach is a reasonable one if you are cleaning up. However, in your case since you know what you want to delete before you start your problem is you have the steps in the wrong order It should be 3 - 2 - 1.

I'm hoping this isn't one of your legacy 2.12 apps because I'm not sure how good the joins were back then.

The method I'd use is to start in DBSys creating a SELECT statement (it doesn't destroy anything if you get it wrong) and you'll want something like

SELECT * FROM subdetail
JOIN detail ON detail.X = subdetail.Y
JOIN master ON master.A = detail.B
WHERE master.FieldA = ValueA

when you've got that working move up to the detail table

SELECT * FROM detail
JOIN master ON master.A = detail.B
WHERE master.FieldA = ValueA

when that's OK replace the SELECT * with DELETE put them in a script and finish with

DELETE FROM master WHERE FieldA = ValueA

wrap the whole lot in a transaction and you're done.

Roy Lambert

Roy Lambert wrote:
> Jerry
>
>
> Adam's approach is a reasonable one if you are cleaning up. However, in your case since you know what you want to delete before you start your problem is you have the steps in the wrong order It should be 3 - 2 - 1.
>
> I'm hoping this isn't one of your legacy 2.12 apps because I'm not sure how good the joins were back then.
>
> The method I'd use is to start in DBSys creating a SELECT statement (it doesn't destroy anything if you get it wrong) and you'll want something like
>
> SELECT * FROM subdetail
> JOIN detail ON detail.X = subdetail.Y
> JOIN master ON master.A = detail.B
> WHERE master.FieldA = ValueA
>
> when you've got that working move up to the detail table
>
> SELECT * FROM detail
> JOIN master ON master.A = detail.B
> WHERE master.FieldA = ValueA
>
> when that's OK replace the SELECT * with DELETE put them in a script and finish with
>
> DELETE FROM master WHERE FieldA = ValueA
>
> wrap the whole lot in a transaction and you're done.
>
> Roy Lambert
>


Thanks, Roy, and Adam, and Tim

Of course it IS my 2.12 legacy app.  That's my only app, really.  I
wrote it first in Applesoft in 1978, then in Turbo Pascal, and didnt
start using an actual database until Delphi + BDE + Paradox came around.
  I actually had someone call me the other day to ask if I could update
the Applesoft program!!!  Those Apple// machines were STURDY!

Jerry the old f**t

Jerry


>Jerry the old f**t

At least there's no "boring" after the old

Roy Lambert

Roy-

One further question about the code below.  There are several subdetail
tables.

That is, a record in the main detail table always has one child, but
that child could be in any one of 4 different tables.  How does that
impact the code you gave me?

Jerry



Roy Lambert wrote:
> Jerry
>
>
> Adam's approach is a reasonable one if you are cleaning up. However, in your case since you know what you want to delete before you start your problem is you have the steps in the wrong order It should be 3 - 2 - 1.
>
> I'm hoping this isn't one of your legacy 2.12 apps because I'm not sure how good the joins were back then.
>
> The method I'd use is to start in DBSys creating a SELECT statement (it doesn't destroy anything if you get it wrong) and you'll want something like
>
> SELECT * FROM subdetail
> JOIN detail ON detail.X = subdetail.Y
> JOIN master ON master.A = detail.B
> WHERE master.FieldA = ValueA
>
> when you've got that working move up to the detail table
>
> SELECT * FROM detail
> JOIN master ON master.A = detail.B
> WHERE master.FieldA = ValueA
>
> when that's OK replace the SELECT * with DELETE put them in a script and finish with
>
> DELETE FROM master WHERE FieldA = ValueA
>
> wrap the whole lot in a transaction and you're done.
>
> Roy Lambert
>

Jerry


You'd need one line of the script for each of the subdetail tables. So you might have

SELECT * FROM subdetailA
JOIN detail ON detail.X = subdetailA.Y
JOIN master ON master.A = detail.B
WHERE master.FieldA = ValueA;

SELECT * FROM subdetailB
JOIN detail ON detail.X = subdetailB.Y
JOIN master ON master.A = detail.B
WHERE master.FieldA = ValueA;

SELECT * FROM subdetailC
JOIN detail ON detail.X = subdetailC.Y
JOIN master ON master.A = detail.B
WHERE master.FieldA = ValueA;

SELECT * FROM subdetailD
JOIN detail ON detail.X = subdetailD.Y
JOIN master ON master.A = detail.B
WHERE master.FieldA = ValueA;


Its a bit more code, it'll take a bit longer but the basic principles are exactly the same.

Roy Lambert

> Looks good to me - it will delete all children with no parent.

Thanks Tim.I was pretty sure it was right, but always good to get a second
op' when you're telling someone to execute the ol' Delete SQL command.

Avagoodweekend!

Adam.

Thanks again, Roy.
I'm just about ready to leave for a vacation, so I will try this when I
get back.
Jerry

Roy Lambert wrote:
> Jerry
>
>
> You'd need one line of the script for each of the subdetail tables. So you might have
>
> SELECT * FROM subdetailA
> JOIN detail ON detail.X = subdetailA.Y
> JOIN master ON master.A = detail.B
> WHERE master.FieldA = ValueA;
>
> SELECT * FROM subdetailB
> JOIN detail ON detail.X = subdetailB.Y
> JOIN master ON master.A = detail.B
> WHERE master.FieldA = ValueA;
>
> SELECT * FROM subdetailC
> JOIN detail ON detail.X = subdetailC.Y
> JOIN master ON master.A = detail.B
> WHERE master.FieldA = ValueA;
>
> SELECT * FROM subdetailD
> JOIN detail ON detail.X = subdetailD.Y
> JOIN master ON master.A = detail.B
> WHERE master.FieldA = ValueA;
>
>
> Its a bit more code, it'll take a bit longer but the basic principles are exactly the same.
>
> Roy Lambert
>