Update Rows - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Update Rows

Author

Topic

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2009-01-21 : 17:52:51

I may be having a brain freeze now, but in this example below, how do I update the NULL rows with the first non null value in the same column above for the same account number

For example in set 1, subscriber for act 3 should all be 31.

Thanks for your help in advance

act subcriber pat1 pat
3 31 31 JOH2
3 NULL 32 SUSI2
3 NULL 33 MAR2
3 NULL 34 DAVI2
3 NULL 35 BILL2
3 NULL 36 RA2
2 21 21 JOHN
2 NULL 22 SUSIE
2 NULL 23 MARY
2 NULL 24 DAVID
2 NULL 25 BILLY
2 NULL 26 RAY

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-01-21 : 18:04:54

something like this:
update t1
set Subcriber = t2.MaxSubcriber
from
YourTable t1
join
(
select act, max(subcriber) as MaxSubcriber
from YourTable
group by act
) t2 on t1.act = t2.act

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2009-01-21 : 18:07:08

That works perfect!! Thanks for the solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources