Query to update rowNum
Posted
by BrokeMyLegBiking
on Stack Overflow
See other posts from Stack Overflow
or by BrokeMyLegBiking
Published on 2010-05-07T05:28:17Z
Indexed on
2010/05/07
5:38 UTC
Read the original article
Hit count: 204
tsql
Can anyone help me write this query more efficiently?
I have a table that captures TCP traffic, and I'd like to update a column called RowNumForFlow which is simly the sequential number of the IP packet in that flow. The code below works fine, but it is slow.
declare @FlowID int
declare @LastRowNumInFlow int
declare @counter1 int
set @counter1 = 0
while (@counter1 < 1)
BEGIN
set @counter1 = @counter1 + 1
-- 1)
select top 1
@FlowID = t.FlowID
from Traffic t
where t.RowNumInFlow is null
if (@FlowID is null)
break
-- 2)
set @LastRowNumInFlow = null
select top 1
@LastRowNumInFlow = RowNumInFlow
from Traffic
where FlowID=@FlowID and RowNumInFlow is not null
order by ID desc
if @LastRowNumInFlow is null
set @LastRowNumInFlow = 1
else
set @LastRowNumInFlow = @LastRowNumInFlow + 1
update Traffic set RowNumInFlow = @LastRowNumInFlow
where ID = (select top 1 ID from Traffic where
flowid = @FlowID and RowNumInFlow is null)
END
Example table values after query has run:
ID FlowID RowNumInFlow 448923 44 1 448924 44 2 448988 44 3 448989 44 4 448990 44 5 448991 44 6 448992 44 7 448993 44 8 448995 44 9 448996 44 10 449065 44 11 449063 45 1 449170 45 2 449171 45 3 449172 45 4 449187 45 5
© Stack Overflow or respective owner