Update question - 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 2000 Forums

Transact-SQL (2000)

Update question

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-16 : 14:44:22

SELECT SegmentID, SegmentTime
FROM Segment
WHERE (VideoID = 35)

What I have here is say three records are returned like so
SegmentID | SegmentTime
1 | 600
2 | 500
3 | 300

I want to update the segment time to include the value of the preceding segmentime(s)

1 | 600 (same)
2 | 1100 (600 + 500)
3 | 1400 (600 + 500 + 300)

Is that possible? It would save a ton of man power...Thanks for looking at it

Harry C

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-16 : 14:48:03

[code]
SELECT
SegmentID,
SegmentTime,
newSegmentTime = (Select sum(SegmentTime) From Segment Where VideoID = 35 and SegmentId <= A.segmentId)
FROM Segment A
WHERE (VideoID = 35)
[/code]

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-16 : 15:02:16

How would I convert that to an Update?

crap, AND I am wrong about the output I want

1 | 0
2 | 600 - value from seg1
3 | 1100 - value from seg 1 and 2

Last thing, I also have a sequence number in there. I want to update every sequence but the FIRST one to a new value, the first one should be 0 or NULL
SELECT SegmentID, SegmentTime, SequenceNumber
FROM Segment
WHERE (VideoID = 35)

Sorry about that...Thanks SeventhKnight!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-16 : 15:25:49

[code]
Update Segment
Set SegmentTime = (Select sum(SegmentTime) From Segment A Where VideoID = 35 and SegmentId < Segment.segmentId)
FROM Segment
WHERE (VideoID = 35)

Select * From Segment Where VideoID = 35
[/code]

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-16 : 15:30:34

quote:
Originally posted by Seventhnight

Update Segment
Set SegmentTime = (Select sum(SegmentTime) From Segment A Where VideoID = 35 and SegmentId < Segment.segmentId)
FROM Segment
WHERE (VideoID = 35)

Select * From Segment Where VideoID = 35

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

as usualy, you're the man. Thanks Corey..

Harry C

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources