Using CTE to delete records 1

[tshad]

I have a CTE that gets my records recursively.

I now need to delete the records in reverse order (level n - 0) because of the Foreign key constraints.

I am looking at using a cursor from the table it creates but want to use a better method if possible.

The code I am using is following.

******************************
DECLARE @MultipleAnchors TABLE(
ParentID int NULL,
ID int NOT NULL );

INSERT INTO @MultipleAnchors
VALUES
(NULL, 1),
(1, 3),
(1, 4),
(4, 5);

SELECT ParentID, ID
FROM @MultipleAnchors

;WITH MultipleAnchorRowsCTE
AS
(
SELECT ParentID, ID, 0 AS level
FROM @MultipleAnchors
WHERE ParentID IS NULL

UNION ALL

SELECT children.ParentID, children.ID, level + 1
FROM @MultipleAnchors children
JOIN MultipleAnchorRowsCTE AS macte
ON macte.ID = children.ParentID
)
SELECT ParentID, ID, Level
FROM MultipleAnchorRowsCTE;
*************************************************

I was thinking of doing a select/into into a temp table then going through the table in reverse order and delete the records one at a time.

Is there a better way to do that?

Thanks,

Tom

 

[markros]

This seems to be a good idea. Alternatively, you can generate the SQL command to delete records using this result and then execute with execute (@SQL)

PluralSight Learning Library

 

[tshad]

But what command could you give to delete the records in sorted order?

It has to delete the children in order until the parent is deleted and there could be many levels.

I can sort them correctly for a cursor but there is no guarantee that they would get them to delete in the proper order even if I did something like:

delete from table1
where col1 in (
select col1,col2
from table2
ORDER BY DESC
)

(which I can't do as it would get me an error as you can't put an ORDER BY in a subquery).

Thanks,

Tom

 

[markros]

Here is what I meant (rough idea):

declare @SQL nvarchar(max)
set @SQL = ''
select @SQL = (SELECT '; 
DELETE FROM MyTable WHERE ID = ' + convert(varchar(10),ID)
from MultipleAnchorRowsCTE ORDER BY Level DESC FOR XML PATH(''),TYPE).value('.','nvarchar(max)')

print @SQL

execute(@SQL) -- it will still delete one record at a time, but you don't need a loop

PluralSight Learning Library

 

[tshad]

I like that.

It worked as expected. And no Cursor. Gotta like that

Thanks,

Tom