Delete from self referencig table ? 1

[whatsthehampton]

Dear all,

From my web app. I am trying to cascade delete entries that are in a self referencing table.

This cte brings back the id's I need

--------------------------------------

declare @idx int
set @idx=1; -- need to pass in the variable here

WITH OrganisationChart (Id, parentid)

AS
(SELECT Id, parentid
FROM ecocv_tbl_sitecontent
WHERE id =@idx
UNION ALL
SELECT sc.Id, sc.parentid
FROM ecocv_tbl_sitecontent sc INNER JOIN OrganisationChart
ON sc.parentid = OrganisationChart.Id)

SELECT id FROM OrganisationChart
--------------------------------------

But I need to access the returned IDs in my delete stored procedure somehow but am stuck.

How can I loop through the return vaules and delete each one please?

Cheers,

J

http://www.iews.co.uk/

ToDo....

 

[bborissov]

I have not much experience with CTE, but how about this:

declare @idx int
set @idx=1; -- need to pass in the variable here

WITH OrganisationChart (Id, parentid)
AS
  (SELECT  Id, parentid
           FROM ecocv_tbl_sitecontent
   WHERE id =@idx      
   UNION ALL       
   SELECT sc.Id, sc.parentid     
          FROM ecocv_tbl_sitecontent sc
   INNER JOIN CTEOrganisationChart
         ON sc.parentid = OrganisationChart.Id)

DELETE FROM ecocv_tbl_sitecontent
FROM ecocv_tbl_sitecontent
INNER JOIN OrganisationChart
      ON ecocv_tbl_sitecontent.Id = OrganisationChart.Id

NOT TESTED!
INNER JOIN

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[whatsthehampton]

Beautiful Borislav !!

A star for you.

Many thanks.

J

http://www.iews.co.uk/