This is, I am sure NOT the best way, but since CTEs

can not be used in SQL 2000 the best I could come up

with is a 3 step approach.

/*This sets your parents status with

multiple children one of which is still ACTIVE and

one of which is INACTIVE , to INACTIVE not what

is wanted but */

UPDATE [PARENT_GUARDIAN] SET PG_STATUS = 'INACTIVE'

WHERE EXISTS(SELECT p.PG_ID,s.SCT_STATUS,s.SCT_ID

FROM [PARENT_GUARDIAN] p

JOIN [dbo].[xref_PG_SCT] x

ON p.PG_ID = x.PG_ID

JOIN SCOUTS s ON

s.SCT_ID = x.SCT_ID WHERE s.SCT_STATUS = 'INACTIVE')

/* We then find and place into a temporarty table

those scouts that are still ACTIVE */

SELECT p.PG_ID

INTO #T

FROM [PARENT_GUARDIAN] p

JOIN [dbo].[xref_PG_SCT] x

ON p.PG_ID = x.PG_ID

JOIN SCOUTS s ON

s.SCT_ID = x.SCT_ID WHERE s.SCT_STATUS = 'ACTIVE'

/* Next we go back and set those parents to ACTIVE */

UPDATE [PARENT_GUARDIAN] SET PG_STATUS = 'ACTIVE'

FROM #T WHERE #T.PG_ID = PARENT_GUARDIAN.PG_ID

/* Check did we obtain the desired results

PG_LNamePG_STATUS

Smith INACTIVE

Smith INACTIVE

Corells ACTIVE

Corells ACTIVE

Galley INACTIVE

Galley INACTIVE */

DROP TABLE #T

A brute force approach - maybe some one can refine it ..