Consolidating records - TSQL problem

drop table dbo.testing
create table dbo.testing (retained int, dropped int)

insert into dbo.testing (retained, dropped)
select 767884, 157441 union all
select 1046261, 157441 union all
select 6699, 157441 union all
select 157441, 73635 union all
select 767884, 73635 union all
select 1046261, 73635 union all
select 6699, 73635 union all
select 1046261, 767884 union all
select 6699, 767884 union all
select 6699, 1046261

;WITH retained AS (
SELECT t.*
FROM testing t
WHERE NOT EXISTS (SELECT 1 FROM testing i WHERE i.dropped = t.retained)
)
SELECT [Level] = 1, l1.retained, l1.dropped
FROM retained l1
UNION ALL
SELECT [Level] = 2, l1.retained, l2.retained
FROM retained l1
inner JOIN testing l2 ON l2.retained = l1.dropped
UNION ALL
SELECT [Level] = 3, l1.retained, l3.retained
FROM retained l1
inner JOIN testing l2 ON l2.retained = l1.dropped
inner JOIN testing l3 ON l3.retained = l2.dropped
UNION ALL
SELECT [Level] = 4, l1.retained, l3.dropped
FROM retained l1
inner JOIN testing l2 ON l2.retained = l1.dropped
inner JOIN testing l3 ON l3.retained = l2.dropped

;WITH retained AS (
SELECT t.retained, dropped
FROM testing t
WHERE NOT EXISTS (SELECT 1 FROM testing i WHERE i.dropped = t.retained)
UNION ALL
SELECT b.retained, t1.dropped
FROM testing AS t1
INNER JOIN retained as b
ON t1.retained = b.dropped)

UPDATE t
SET t.retained = uv.retained
FROM testing t
INNER JOIN retained uv
ON t.dropped = uv.dropped

Lynn Pettis (9/25/2012)

Abu Dina (9/25/2012)

Lynne's solution works if we assume that the retained ID is always the maximum.

But tthe solution doesn't work with the following record set:

drop table dbo.testing 
create table dbo.testing (retained int, dropped int)

insert into dbo.testing (retained, dropped)
select 767884, 157441 union all
select 1046261, 157441 union all
select 6699, 	157441 union all
select 157441, 73635 union all
select 767884, 	73635 union all
select 1046261, 	73635 union all
select 6699, 	73635 union all
select 1046261, 767884 union all
select 6699, 	767884 union all
select 6699, 1046261

I will keep trying to see if I can come up with a solution but if anyone else can think of something then that'd be great!

Thanks in advance.

I can only write code based on what you provided. Based on the sample data and expected results, what I saw was the max id being retained. With the new data, what are the rules for determining what ID is used? Also, you posted additional data but not additional expected results.

with rCTE as (
select t1.retained, t1.dropped, t1.retained as TopLevel
from dbo.testing t1 left outer join dbo.testing t2 on t1.retained = t2.dropped
where t2.dropped is null
union all
select t1.retained, t1.dropped, r.TopLevel
from dbo.testing t1inner join rCTE r on t1.retained = r.dropped)
update tu set
retained = r.TopLevel
from dbo.testing tu inner join rCTE r on tu.retained = r.retained and tu.dropped = r.dropped;

with rCTE as (
select
t1.retained,
t1.dropped,
t1.retained as TopLevel
from
dbo.testing t1
left outer join dbo.testing t2
on t1.retained = t2.dropped
where
t2.dropped is null
union all
select
t1.retained,
t1.dropped,
r.TopLevel
from
dbo.testing t1
inner join rCTE r
on t1.retained = r.dropped)
update tu set
retained = r.TopLevel
from
dbo.testing tu
inner join rCTE r
on tu.retained = r.retained and
tu.dropped = r.dropped;

;WITH rCTE AS (
SELECT [level] = 1, t1.retained, t1.dropped,
--fullchain = CAST(CAST(t1.retained AS VARCHAR(10)) + ',' + CAST(t1.dropped AS VARCHAR(10)) AS VARCHAR(8000)),
Toplevel = t1.retained
FROM dbo.testing t1
LEFT OUTER JOIN dbo.testing t2 ON t1.retained = t2.dropped
WHERE t2.dropped IS NULL
UNION ALL
SELECT [level] = [level] + 1, t1.retained, t1.dropped,
--fullchain = r.fullchain + ',' + CAST(t1.dropped AS VARCHAR(10)),
Toplevel = r.Toplevel
FROM dbo.testing t1
INNER JOIN rCTE r ON t1.retained = r.dropped
)
--SELECT *
--FROM rCTE
--ORDER BY level, dropped

UPDATE tu SET
retained = r.TopLevel
FROM dbo.testing tu
INNER JOIN rCTE r ON tu.dropped = r.dropped;

SELECT * FROM dbo.testing