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

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

johnsql
Posting Yak Master

USA
161 Posts

Posted - 11/12/2007 :  17:09:04  Show Profile  Reply with Quote
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

Sweden
30114 Posts

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

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



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.08 seconds. Powered By: Snitz Forums 2000