Delete childe rows if parent deleted

Tick the "Cascade Delete" box on the referential integrity constraint.

You can use a Foreign key for this and then use On Delete Cascade option to auto delete child records. Read
http://technet.microsoft.com/en-us/library/ms189049.aspx
http://www.w3schools.com/sql/sql_foreignkey.asp

Cascade Delete not allowed on self referential table

Ar, in that case an 'instead of' trigger is what you want.

http://technet.microsoft.com/en-us/library/ms191208(v=sql.105).aspx

thanks for your reply, but do u have a specific answer?

Do you mean can I write the T-SQL for you? Unfortunately I don't have time for that, its very easy if you follow the links.

Try

delete
from tab1 
where ParentFieldID in(select FieldID from tab1)

Select allOpen in new window

@dale_burrell if you don't have time , then don't shoot whatever, your link contains nothing.

@HuaMinChen, need to delete all sub childe and childe for given FieldID

Ethar, I work on the principle of helping you help yourself. I assume you are a professional needing some pointers on how to solve the problem, not a student who doesn't know where to start. That link explains most of what you need to know, a quick google will fill any gaps.

excuse me .... Give a chance to find a solution ....(I don't care about your principles )

Good luck then as your attitude isn't helping you.

delete
from Childtable
where ParentFieldID not in(select FieldID from Parenttable)

I have one table

delete
from tablename
where ParentFieldID not in(select FieldID from tablename)

good Idea, but this delete child and sub child?

i assume the sub child parentid will be the child id, the above query will work if so but will have to be run once fore each level of parent ,child sub child etc,

if the parent is missing it will delete the child, next run through the child will be missing so it will delete the sub child

YES, I know we should run it several time , any solution?

Sorry i think i have missed something. Any solution to what?

I need to Delete all child  and sub child rows if parent row deleted with one statement

does the sub child have the parent id in it or just the child id or is there a level indicator stating if the row is parent child or subchild

no,
ok, can I get a list of all child and sub child rows?

whats in the parent ParentFieldID  column

FieldID

no what value does the parent record ParentFieldID  hold 0, null or ""?

this may help in the future to stop it happening again, this wont help now as the parent records are missing.

So what you are actually asking is how one would delete the child records when the parent record no longer exists?

Assuming that is the case you must not have a foreign key created on the relationship? Because otherwise you wouldn't be able to delete the parents?

i assume that is correct, i am asking ethar1 how the parent is identified once that is known i can write a query to delete all the child and subchild rows

If so then you can use the code I wrote for the trigger but replace this line:

    select Id, @NestLevel from Deleted

Select allOpen in new window

with this line:

    select ParentId, @NestLevel from MyTable X where not exists (select 1 from MyTable Y where Y.Id = X.ParentId)

Select allOpen in new window

that would get all parents and child rows where parents are missing, in effect promoting orphaned child to parent

It would find them all for sure, but not promote them, delete them :)

And we surely must have given ethar1 enough info to solve his problem by now...