Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 update row's column recursively

Author  Topic 

johnsql
Posting Yak Master

161 Posts

Posted - 2007-11-12 : 17:09:04
This question comes from thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85456, but it is extended with another scenario of sample data.


Assume I have a table #t defined as
create table #t (ID int not null, c1 varchar(50) null, c2 varchar(50), c3 varchar(50) not null, c4 varchar(50) not null)

if Column c1 of a row is null, then it must be updated with that of row of ID-1 or recursively.
For example, c1 of row of ID of 9 should be updated by that of row of ID 8, that is 'EmployeeC'. C1 of Row of ID
of 2 is updated by that of row of ID of 1, that is 'EmployeeA'. And c1 of row of ID of 3 is updated by c1 of row of ID of 2 .


-- sample data

insert #t
select 1 , 'EmployeeA', 'UC Berkeley', 2001, ' 10 Arlington RD.'
union all select 2, null, 'UC Davis', 2005, ' 861 Trees Lane'
union all select 3, null, 'UC Santa Cruz', 2007 , '654 Frog St. Apt. 3'
union all select 4, null, 'UCSC', 2001 , '12399 SunSet Blvd. Apt. 3'
union all select 5, 'EmployeeB', 'UCLA', 2001, '10 Wilshire Blvd.'
union all select 6, null, 'UTW', 1999, '100 GreenView Rd.'
union all select 7, null, 'UAG', 1996, '786 OakThreees Dr.'
union all select 8, 'EmployeeC', 'UTH', 2002, '96 Sherman Dr.'
union all select 9, null, 'UWS', 2000, '1226 HWY 55 Suite # 102'

-- expected output
ID c1 c2 c3 c4
----------- -------------------------------------------------- -------------------------------------------------- ----------- --------------------------------------------------
1 EmployeeA UC Berkeley 2001 10 Arlington RD.
2 EmployeeA UC Davis 2005 861 Trees Lane
3 EmployeeA UC Santa Cruz 2007 654 Frog St. Apt. 3
4 EmployeeA UCSC 2001 12399 SunSet Blvd. Apt. 3
5 EmployeeB UCLA 2001 10 Wilshire Blvd.
6 EmployeeB UTW 1999 100 GreenView Rd.
7 EmployeeB UAG 1996 786 OakThreees Dr.
8 EmployeeC UTH 2002 96 Sherman Dr.
9 EmployeeC UWS 2000 1226 HWY 55 Suite # 102


Thank you in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 17:22:16
[code]
select t1.id,
t1.c1,
(select top 1 t2.c1 from #t as t2 where t2.id <= t1.id and t2.c1 is not null order by t2.id desc) as c1,
t1.c2,
t1.c3,
t1.c4
from #t as t1[/code]


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

- Advertisement -