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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 update row's column recursively
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

161 Posts

Posted - 11/12/2007 :  17:09:04  Show Profile  Reply with Quote
This question comes from thread, 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.

Patron Saint of Lost Yaks

30421 Posts

Posted - 11/12/2007 :  17:22:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote

	(select top 1 t2.c1 from #t as t2 where <= and t2.c1 is not null order by desc) as c1,
from	#t as t1

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000