CODEWITHSUNDEEP
sqlsql-serversql-delete
Tom Gullen
Tom Gullen

Reputation: 61755

SQL delete records in order

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?

Upvotes: 1

Views: 5174

Answers (3)

Philip Kelley
Philip Kelley

Reputation: 40339

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.

Upvotes: 5

benjamin moskovits
benjamin moskovits

Reputation: 5458

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

Upvotes: 0

Joseph Linkous
Joseph Linkous

Reputation: 3

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.

Upvotes: 0

Related Questions