Hierarchy update children when parent

[Sammy145]

Hi

what i want is query that will update all children to 'N' if the parent [ACTIVE] field is 'N', i want all
children to be ACTIVE ='N'
--The hierarchy can be 10 levels down
--parent is 0 for top level

CREATE TABLE [dbo].[Hierarchy](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NULL,
[descr] [varchar](50) NULL,
[maingroup] [varchar](12) NULL,
[subgroup] [varchar](12) NULL,
active [varchar](2) NULL
)


Can someone show me how to do this please

Thanks for any help!

 

[bborissov]

What version of SQL Server you use?
If you use SQL Server 2005 or later you could try recursive SELECT (seek for CTE in BOL) Then store the result from that select in Temp Variable and then join that table to your main table and do the update.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Sammy145]

sql 2005 i created a c# solution thanks