September 12, 2008 at 10:48 am
Currently I have a CTE that I want to use to delete data from two tables. Do I also have to use a variable table in order to preserve the data from the CTE? That is what MSDN says. I can only have one statement after the CTE. Is there a solution to this?
BEGIN
WITH tree (id)
AS (
SELECT id
FROM T
WHERE [ID] = @ID
UNION ALL
SELECT id
FROM T
INNER JOIN tree t
ON t.id = Pid)
DELETE FROM [dbo].table1
WHERE
ID IN (SELECT id FROM tree)
DELETE FROM [dbo].table2
WHERE
ID IN (SELECT id FROM tree)
END
[/Code]
September 12, 2008 at 11:15 am
If you can be sure every ID in the CTE will be in the first table you are deleting from, you could use OUTPUT.
September 12, 2008 at 11:18 am
The CTE is part of the statement. Duplicate the CTE in both statements.
September 12, 2008 at 12:21 pm
The output will not work since I am deleting the children first and I am not sure if the values will be in that table. Since there are multiple tables that I am deleting from with the CTE is a variable table better to use and delete from the variable table?
ie
Declare @Table as Table
Insert into @Table Select Id From tree
delete from table1 where id In (Select id From @Table)
delete from table2 where id In (Select id From @Table)
September 12, 2008 at 12:26 pm
That's probably your safest option.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply