How to Delete a Chile Records from a Parent table ?

[hellboss]

Hi everyone !
Im using sq05

I have a set of table which goes to 5 levels of Transation ,
ex:

• Parent 1

• Parent 2(Child of Parent1)

• Parent 3(Child of Parent2)

• Parent 4(Child of Parent3)

• Child (Child of Parent4)

Each Tables have their Corresponding Primary key & foreign key Relationships,
What Happens is when i tried to Delete a Parent2 Record the Corresponding Child Records of Parent2 and in turn all the Corresponding records of Parent3 should be Deleted ,

Is their any Query to Satisfy this Condition.

Kindly provide me with a valid Solution

Thanks in Advance !!

[ck9663]

Hi everyone !
Im using sq05

I have a set of table which goes to 5 levels of Transation ,
ex:

• Parent 1

• Parent 2(Child of Parent1)

• Parent 3(Child of Parent2)

• Parent 4(Child of Parent3)

• Child (Child of Parent4)

Each Tables have their Corresponding Primary key & foreign key Relationships,
What Happens is when i tried to Delete a Parent2 Record the Corresponding Child Records of Parent2 and in turn all the Corresponding records of Parent3 should be Deleted ,

Is their any Query to Satisfy this Condition.

Kindly provide me with a valid Solution

Thanks in Advance !!

you can either delete the youngest to the oldest until you reached the parent record, or checkout the CASCADE keyword

[hellboss]

you can either delete the youngest to the oldest until you reached the parent record, or checkout the CASCADE keyword

Thanks For your reply !

Techincally i cant Delete starting from the Child but, Wil Check out the Cascade ! Can you provide me with a sample Query on Cascade?

Thanks in Advance

[debasisdas]

try for this sample query also

[CODE=sql]DELETE FROM Z WHERE Z-ref IN
(SELECT Z_ref FROM Y WHERE Y_ID IN
(SELECT Y_ID FROM X WHERE field1 = '234'))[/CODE]

[Afterlife]

Techincally i cant Delete starting from the Child but, Wil Check out the Cascade ! Can you provide me with a sample Query on Cascade?

Books Online is your friend: Cascade isn't a keyword you can use in your T-SQL queries, but part of the definition of your foreign keys.
But take care, in a lot of cases cascading deletes is not what you want.
Eg, if you want to remove a customer from your database, cascading deletes will also remove buys, invoices, etc. in the past, which most probably isn't what you want.

So use cascading deletes with care!