Update Query - 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 Query

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-07-30 : 05:01:20

[code] hi

I have table like this...

A B C D E
1 5 T
2 6 F
3 7 T
4 3 T
5 6 T

In column D i want the total upto C column value is 'F' which means (5+6)=11
And How to update the values like E column(values based on A col)....

Like this...

A B C D E
1 5 T 0 1
2 6 F 11 1:2
3 7 T 0 1:2:3
4 3 T 0 1:2:3:4
5 6 T 0 1:2:3:4:5

Pls help me out.. [/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-30 : 06:02:40

[code]

declare @tab table(A int, B int, C char(1), D int, E varchar(32))
insert into @tab (a,b,c) select
1, 5, 'T' union all select
2, 6, 'F' union all select
3, 7, 'T' union all select
4, 3, 'T' union all select
5, 6, 'T'

select * from @tab

update t
set t.d = s.val
from @tab t
inner join (
select t.a,sum(c.b) as val
from @tab t inner join @tab c on c.a <= t.a
where t.c ='F' group by t.a ) s on s.a = t. a

;with cte(a,id )
as
(
select top 1 a , CAST(a AS VARCHAR(801) ) from @tab

union all

select t.a, cast(c.id as varchar(400)) + ':'+ cast(t.a as varchar(400))
from @tab t
inner join cte c on c.a+1 = t.a
)
update t
set t.e = c.id
from @tab t
inner join cte c on c.a = t.a

select * from @tab
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources