Solved

Delete childe rows if parent deleted

Posted on 2013-12-05
31
310 Views
Last Modified: 2013-12-17
Dear all,
How Delete all childe rows if parent row deleted.

Table:
FieldID
ParentFieldID

(MS sql 2008)
0
Comment
Question by:ethar1
  • 10
  • 10
  • 9
  • +1
31 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39700103
Tick the "Cascade Delete" box on the referential integrity constraint.
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 39700105
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
0
 

Author Comment

by:ethar1
ID: 39700109
Cascade Delete not allowed on self referential table
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39700111
Ar, in that case an 'instead of' trigger is what you want.

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

Author Comment

by:ethar1
ID: 39700120
thanks for your reply, but do u have a specific answer?
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39700123
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.
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 39700124
Try
delete
from tab1 
where ParentFieldID in(select FieldID from tab1)

Open in new window

0
 

Author Comment

by:ethar1
ID: 39700136
@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
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39700145
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.
0
 

Author Comment

by:ethar1
ID: 39700150
excuse me .... Give a chance to find a solution ....(I don't care about your principles )
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39700154
Good luck then as your attitude isn't helping you.
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39700159
delete
from Childtable
where ParentFieldID not in(select FieldID from Parenttable)
0
 

Author Comment

by:ethar1
ID: 39700171
I have one table
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39701508
delete
from tablename
where ParentFieldID not in(select FieldID from tablename)
0
 

Author Comment

by:ethar1
ID: 39704279
good Idea, but this delete child and sub child?
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 9

Expert Comment

by:QuinnDex
ID: 39704300
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
0
 

Author Comment

by:ethar1
ID: 39705770
YES, I know we should run it several time , any solution?
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39706887
Sorry i think i have missed something. Any solution to what?
0
 

Author Comment

by:ethar1
ID: 39707797
I need to Delete all child  and sub child rows if parent row deleted with one statement
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39707911
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
0
 

Author Comment

by:ethar1
ID: 39711511
no,
ok, can I get a list of all child and sub child rows?
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39712040
whats in the parent ParentFieldID  column
0
 

Author Comment

by:ethar1
ID: 39712368
FieldID
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39712456
no what value does the parent record ParentFieldID  hold 0, null or ""?
0
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 500 total points
ID: 39713148
OK, let me put your out of your misery. You can either create this trigger which will automatically delete all the children, or you can use the code within the trigger and create a stored procedure to delete a record and all its children. Up to you.

This will delete the children to *any* level of nesting. I've shown you the table structure I've based it on, and will leave it up to you to translate that to your own situation.

Hopefully it all makes sense. I haven't tested it so you will need to carry out suitable tests and resolve any issues that arise.

Good luck!

-- create table MyTable (Id uniqueidentifier, ParentId uniqueidentifier, ...) -- Based on this table structure

CREATE TRIGGER dbo.MyTable_MyDeleteTrigger
  ON dbo.MyTable
  instead of DELETE
AS 
BEGIN
  SET NOCOUNT ON

  declare @NestLevel int
  -- Create a temp table to build a list of all the rows we need to ultimately delete. Store the nesting level so we can delete them in reverse order.
  declare @Deletions table (Id varchar(38), NestLevel int)
   
  set @NestLevel = 0
  
  -- Add the id's of the top level records to delete
  insert into @Deletions (Id, NestLevel)
    select Id, @NestLevel from Deleted

  -- Iterate through as many child levels as there are, adding them along with the nesting level to the temp table.
  -- We're always looking to see if there are any children of the records added at the latest nesting level.
  while exists (select 1 from MyTable where ParentId in (select Id from @Deletions where NestLevel = @NestLevel)) begin
    insert into @Deletions (Id, NestLevel)
      select Id, @NestLevel+1 from MyTable where ParentId in (select Id from @Deletions where NestLevel = @NestLevel)
    set @NestLevel = @NestLevel + 1 -- Incease the nesting level each time through the loop
  end

  -- Delete them from the highest nesting level to the lowest so that we never break referential integrity
  while @NestLevel > -1 begin
    delete from MyTable where Id in (select Id from @Deletions where NextLevel = @NestLevel)
    set @NestLevel = @NestLevel - 1
  end
END
GO

Open in new window

0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39713301
this may help in the future to stop it happening again, this wont help now as the parent records are missing.
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39713318
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?
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39713321
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
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39713327
If so then you can use the code I wrote for the trigger but replace this line:
    select Id, @NestLevel from Deleted

Open 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)

Open in new window

0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39713339
that would get all parents and child rows where parents are missing, in effect promoting orphaned child to parent
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39713342
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...
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now