SQL delete records in order

Question

Given the table structure:

Comment
-------------
ID (PK)
ParentCommentID (FK)

I want to run DELETE FROM Comments to remove all records.

However, the relationship with the parent comment record creates a FK conflict if the parent comment is deleted before the child comments.

To solve this, deleting in reverse ID order would work. How do I delete all records in a table in reverse ID order?

See this previous [SO answer](http://stackoverflow.com/a/18616544/1260204). Basically drop the current FK constraint and re-add it with cascade delete option. This will allow a single delete to remove records from both tables. — Igor, Apr 15 '16 at 15:14

score 5 · Accepted Answer · answered Apr 15 '16 at 15:32

The following will delete all rows that are not themselves parents. If the table is big and there's no index on ParentCommentID, it might take a while to run...

DELETE Comment
 from Comment co
 where not exists (--  Correlated subquery
                   select 1
                    from Comment
                    where ParentCommentID = co.ID)

If the table is truly large, a big delete can do bad things to your system, such as locking the table and bloating the transaction log file. The following will limit just how many rows will be deleted:

DELETE top (1000) Comment  --  (1000 is not very many)
 from Comment co
 where not exists (--  Correlated subquery
                   select 1
                    from Comment
                    where ParentCommentID = co.ID)

As deleting some but not all might not be so useful, here's a looping structure that will keep going until everything's gone:

DECLARE @Done int = 1

--BEGIN TRANSACTION

WHILE @Done > 0
 BEGIN
    --  Loop until nothing left to delete
    DELETE top (1000) Comment
     from Comment co
     where not exists (--  Correlated subquery
                       select 1
                        from Comment
                        where ParentCommentID = co.ID)
    SET @Done = @@Rowcount

 END

--ROLLBACK

This last, of course, is dangerous (note the begin/end transaction used for testing!) You'll want WHERE clauses to limit what gets deleted, and something or to ensure you don't somehow hit an infinite loop--all details that depend on your data and circumstances.

score 0 · Answer 2 · edited May 23 '17 at 12:31

0

With separate Parent and Child tables, ON DELETE CASCADE would ensure that deleting the parent also deletes the children. Does it work when both sets of data are within the same table? Maybe, and I'd love to find out!

How do I use cascade delete with SQL server.

edited May 23 '17 at 12:31

Community

1
1

answered Apr 15 '16 at 15:20

Joseph Linkous

3
3

benjamin moskovits · Answer 3 · 2016-04-15T15:30:49.350

0

this works (you can try replacing the subquery with top...)

create table #a1 (i1 int identity, b1 char(5))
insert into #a1 values('abc')
go 5 

while ( (select count(*) from #a1 ) > 0)
    begin 
     delete from #a1 where i1=(select top 1 i1 from #a1 order by i1 desc)

    end

edited Apr 15 '16 at 15:30

answered Apr 15 '16 at 15:25

benjamin moskovits

5,261
1
12
22

SQL delete records in order

3 Answers3